Reputation: 309
I am a beginner developer and i would like to ask some advice.
I am currently building a platform where people will be allowed to upload images and tag them.
I was reading through some articles with the following structure to store tags
Storing Logic 1
| photo_id | name | tags |
| 1 | some photo | flower, sun. island, beach |
| 2 | some photo2 | hawaii, travle. surf |
Lot of people said this is not such a good idea
So my logic.
I was reading around about Many-to-Many relations and i came up with this logic
Tags table
| tag_id | name |
-----------------------
| 1 | flower |
| 2 | hawaii |
| 3 | surfing |
| 4 | island |
| 5 | travel |
Photos table
| photo_id | name |
---------------------------
| 1 | some photo |
| 2 | some photo2 |
Relation table
| tag_id | photo_id |
---------------------------
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 2 |
I have chosen to use Laravel framework to make the development easier
But my problem is with logic 2 and what i am scared of is it will generate a huge load time.
Because there will be no default just user based tags i thought about the following logic.
User uploads the image with tags, before image is saved, check if the actual tags exsit if not save it, than return tags_id
and save it to the relation table with photo_id
So i have 2 questions
Which logic is better and why?
If logic 2, is it good the way i thought it up? and should i worry about the load time in the future when lot of tags will be there?
thank you
Upvotes: 4
Views: 4085
Reputation: 5093
In your second example, your relation table
should have indexes, so that when you look for all the tags based on a specific photo_id, the answer will be rapidly returned.
See also Foreign Keys
In your relation
table, tag_id
is a foreign key into your tag table and photo_id
is a foreign key into the photo table. Tags may have a relationship to more than 1 photo and a photo may have a relationship to more than one tag.
Similarly the names of your tags (and photos) should also be indexed for rapid searching.
Upvotes: 1
Reputation: 3326
I would go with the second one. I wouldn't worry about load times. You can easily get the categories with joins.
However, you should add an id column on the relation table so that multiple images can share a category.
Upvotes: 2