I have a list of checkboxes in my form, user may chose any of them, or just all of them.
Think that user selects the type of sport he is interested.
I need the best database structure to store this user choise. So that, in future I can get all this data.
I think, I just can store each (userID, sport) choise as a new row in database table. But it is confusing me, because table will expand faster with just a few number of users.
Any ideas, brothers?
You can setup a many-to-many table such as:
FavoriteSports
------
id user_id sport_id
1 5 20
Where you have:
User
-------
id name
5 Mike
Sport
-----
id name
20 Football
This makes sense because a user has many sports, and a sport has many users.
Deciding how to do this is called normalizing.
There are multiple ways to do this depending on how normalized you want your data.
The simplest way is what you described.
userID userName sport
Or you can have 2 tables
users
userID userName sportID
sports
sportID sport
Or you can have 3 tables
users
userID sportName
sports
sportID sportName
user_sports
userID sportID
Where the user_sports table contains which user likes which sport.
Which method you chose depends on the relationships of your data and how much duplication you expect.
If you are only storing which sport a user has chosen, I would choose the second one. That prevents duplication of sport names but only allows one sport per user. If you want to allow users to choose multiple sports, use the third option.
