CODEWITHSUNDEEP
phphtmlmysqldata-structurestags
Levi
Levi

Reputation: 309

PHP & MySQL Tagging system logic

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

thank you

Upvotes: 4

Views: 4085

Answers (2)

vogomatix
vogomatix

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

Brobin
Brobin

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

Related Questions