Search code examples
mysqldatabasedatabase-designrelational-databaseentity-relationship

Deciding database table architecture

I'm very new to Databases and looking forward to develop an application with some advanced functionality.

Here I'm going to have a table with users just like the following one. enter image description here

So, in the sports column, value will be the sports the user plays. They will be some ids from the records of sports table.

So my problem is how to store those multiple values in the same row same column ?

Thanks

PS: Please note that I'm a beginner...

Solution

  • The proper way to do this would be to maintain three different tables.

    User: id, name, age

    Sport: id, name

    UserSport: user_id, sport_id

    The UserSport table references the primary key (id) of both tables (User and Sport) and contains a separate entry for each sport the user participates in.

    The primary key of UserSport should then be a composite key of both user_id and sport_id. This allows you to have multiple rows for each user and multiple rows for each sport, but a unique combination of both the user and sport.