0

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

Francesco Raff Anselmi
  • 5
  • 2
  • Association tables or [Junction Tables](http://stackoverflow.com/a/32620163). So one-to-many or many-to-many. – Drew Jul 22 '16 at 15:10

1 Answers1

1

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

Twelfth
  • 7,070
  • 3
  • 26
  • 34
  • Depending on the data and purpose, you might as well skip the third table and just set the hobby-data directly into the userhobby table. `hobbyId|userId|hobbyName|hobbyDescription` ... though `choose` means, there has to be a separate hobby table, out of which the user can choose from, so nvm – Philipp Jul 22 '16 at 15:03
  • @Philipp - it works, but you leave a lot of redundant data in your system as you'll need to be repeatedly entering the hobbyname and description text if several users have the same hobby. You will also make quieries in the hobby table slower...where hobby_id = 5 is much more effiecient than where hobbyname = 'stackexchange' – Twelfth Jul 22 '16 at 15:08