inserting into database with multiple tables
i am trying to build a database that allows photo tagging.I want that the photos should display the tags and when clicked all other photos that have the same tags should be displayed.One way of doing it is using a look up table that stores the id of both the tags and the photos,but this would require inserting into atleast 3 tables(photo table,tag table,look up table and also i have to search first weather that tag is already there or not) when a photo is uploaded.And also it would take more time when i am displaying the images related to a particular tag.The second way i think is making a tag table and that stores the id of the photo table as a foreign key and the tag name.Here i have to update in just two tables with no searching and when displaying i am using only two tables to match the particular tag entry. Help me figure out the problem and also suggest if some other solution possible.The speed of insert and display is important
Your photos and tags seem to have what is referred to as a many-to-many relationship. Photos can have many tags and tags can have many photos.
Because of this you will indeed need to have a third table to represent this relationship. This is commonly called a join table because of its use in JOINing the two proper tables in queries.
It's true that this means there is one more table to maintain, but this is the only way to model such a relationship (in most DBMS's including MySQL).
When a tag is added to a photo, look up that tag and if it doesn't exist create it. Then insert a row into the join table with that tag's ID and the photo ID.
You should create three tables.
photos, tags and photo_tags. Where photo_tags is your reference table.
This allows photos and tags to have a many-to-many relationship.
Its ok to use three tables. In real life, we use many many tables, and databases are very fast if you write your select statements with proper joins.