CODEWITHSUNDEEP
mysqlsql
Francesco Raff Anselmi
Francesco Raff Anselmi

Reputation: 5

Manage multi-valued attributes SQL

I'm trying to create a user registration system. During the registration process the user can choose different hobbies. I must therefore manage the multi-valued attribute 'hobbies'. The users table has the field 'username' (varchar) as the primary key. I was thinking of creating another table called 'hobbies', how can I connect each user to its hoobies? Any suggestions?

Thankyou

Upvotes: 0

Views: 249

Answers (1)

Twelfth
Twelfth

Reputation: 7190

Youll need 3 tables. First recommendation is to change your primary key to a userID (integer, auto-increment recommended) and not username. Names don't function well as keys.

Second table is you userhobby table that will link a userID to a hobby. I recommend hobby_id and user_id as your two field here...it's a mapping table and really only needs to contain IDs and possibly date fields / valid fields if you want a user to be able to remove a hobby but you don't want to delete the actual data. Use 2 fields as the primary key, user_id + hobby_id.

Third and final table is Hobby that maps the hobby_id to an actual hobby. Hobby_id (primary key, int, and autoincrement preferred) and then a hobby name and whatever information you want to specify for that hobby.

Make sense? The setup will allow for a user to have multiple hobbies and keep down redundant data

Upvotes: 1

Related Questions