PHP & MySQL Tagging system logic

Question

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

You should not worry about the load time because you will hopefully know all about database indices and how they help prevent the time escalating... :-) — vogomatix
– vogomatix, Commented Jun 5, 2014 at 15:03
It wont be a problem for example when people search for an actual tag? — Levi
– Levi, Commented Jun 5, 2014 at 15:06
You can index the tag names as well... additions are slower, searches are faster as a result. — vogomatix
– vogomatix, Commented Jun 5, 2014 at 15:09

Brobin · Accepted Answer · 2014-06-05 15:08:00Z

2

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.

answered Jun 5, 2014 at 15:08

Brobin

3,3362 gold badges21 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

vogomatix · Accepted Answer · 2014-06-05 15:24:20Z

1

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.

Collectives™ on Stack Overflow

PHP & MySQL Tagging system logic

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related