MySQL structure for large number of fields

Question

I have a MySQL database where one table stores user information and another table stores something called as 'tags' for each user. The structure is something like this:

Table 1: user_info

id | col1 | col2 | ....

Table 2: tags

id | user_id | tag

The approximate number of users would be around 25000, and practically speaking each user would have like 50 tags but theoretically the user can create infinite tags. Each user can only access the tags he/she created, so I paired it with user_id in Table 2.

So my question is that is the structure I have a good structure, or is there a better way to do this? (Let me know if this is not the right place to ask, since this is not a direct code question.)

i'll prefer the current design since you mentioned that a user can have infinite tags. — John Woo
– John Woo, Commented May 10, 2013 at 2:55

JPR · Accepted Answer · 2013-05-10 04:22:43Z

3

You could also make a tags table and a relationship table to relate the users table to the tags table. You would have a many-many relationship between users and tags through the relationships table.

A tag table: id, name, etc. A user table: id, name, etc. A relationship table: user_id, tag_id, with foreign keys tied to tag.id and user.id, and a pk on tag.id and user.id.

As far as I know that's probably the best way to do it, you'll have one row for each tag, one row for each user, and a table with a row for each relationship between a user and a tag.

Depending on what you're using for the app side, you can usually do some neat relationships between the models - a many-many-through scenario in active record or something similar, and make things really easy on yourself.

edited May 10, 2013 at 4:22

answered May 10, 2013 at 2:57

JPR

8694 silver badges7 bronze badges

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

1 Comment

rgamber Over a year ago

Thanks, I will look into for more details on the relationship tables.

Collectives™ on Stack Overflow

MySQL structure for large number of fields

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related