Database structure for tags?

Posted on

Question :

I posted a thread on Stack Overflow and one person pointed out that my database structure wasn’t the best so now I’m here to ask how to do this in a good and proper way.

Currently I have one table called “users” and in the table I have a lot of userinfo. In “users” I have a column called “tags” were information inside the column look like “#dj #personal #coding” and things like that for every user. Is there any better way to store tags describing the user? I have one column called “tags” were the user have stored tags describing him/her and also a column called “interests” were tags are stored to remember what the user is looking for…

Basically, is there any better way to store these tags for each user?

Answer :

It makes sense to store tags themselves in a separate table, and add a link table to implement many-to-many relationship between users and tags, for example

users (user_id (PK), name, [other user attributes]);
tags(tag_id(PK), tag_name, [other tag attributes]);
users_tags(user_id (FK to users) ,tag_id (FK to tags), PK(user_id,tag_id));

Depends on your needs, users_tags may have other attributes as well, for instance you might want to add datetime column to store when user added tag (surely, if it has any value to your application)

Leave a Reply

Your email address will not be published. Required fields are marked *