CODEWITHSUNDEEP
mysqldatabase-designtags
morgi
morgi

Reputation: 1025

Database design to assign specific tags to an item

I am trying to build a little system that would assign specific tags to an item, or a person to be precise. So, I have a list of persons and a list of tags. I need to assign 3 specific tags to each person (that correspond to 3 different skills this person might have).

In a nutshell, the output would look like this :

Person 1 | webdesign, ux, jquery
Person 2 | blogging, photography, wordpress
Person 3 | graphic-design, 3d, inventor
...

For now, those lists are stored in two different tables :

persons
-------
person_id
person_name

tags
-------
tag_id
tag_name

My main goal is to avoid repetition and to simply assign 3 existing tags to an existing person.

Could you give me a few hints on how to implement this? I know that a three-table design is common for a tagging system, but is it relevant in my situation?

Thanks for your help.

Upvotes: 0

Views: 316

Answers (3)

doublesharp
doublesharp

Reputation: 27599

If you want to ensure that you don't have any duplicates and to be able to add N tags to a person, then to properly implement a normalized design you would need a third table to link the tags to each person

persons_2_tags
--------------
person_id
tag_id

To guarantee uniqueness, you can either use a composite primary key, or add a unique index to the table including both columns.

See an example of the above in this SQL Fiddle.

If you need to enforce the 3 tag limit at the database level, you can add a third column to the persons_2_tags table (tag_number for example) that is an enum with values of 1, 2, 3 and add that to your unique index. Insert logic would need to be handled at the application level, but would be enforced by the index.

Upvotes: 3

nickf
nickf

Reputation: 545995

I think that I would probably do the three table design which Jeff O mentioned, however, just to present an alternative view...

If you're just talking about tags, that is, a short string with no other meta data, I don't know that you'd need a tags table. Essentially, the tag itself could be the its id.

persons (person_id, person_name);
tags (person_id, tag);

Yeah, you'd get a bit of repetition there, but they're short strings anyway and it should really make a difference.

Upvotes: 0

JeffO
JeffO

Reputation: 8043

Do your requirements specify "exactly" 3 tags?

The third table is recommended to stay normalized. It's a typical Many-to-many relationship. This offeres the greatest amount of flexibility since you can have an unlimited, yet unique list of user/tag pairs.

You could have 3 columns in the user table for each tag. Performance would be improved at the expense of flexibility. Queries like, "List all the users with tag = 'X'" are a little harder. There may be several null values if you allow fewer than 3 tags. Of course in this setup, you'll have to create a new column and a lot of code to expand beyond three columns.

Upvotes: 1

Related Questions