CODEWITHSUNDEEP
sqlnormalizationone-to-manyforeign-key-relationship
dmzkrsk
dmzkrsk

Reputation: 2115

Database design. One-to-many extra attributes

Let's say we have a Player table and a Team table and one-to-many relationship between them (a Player is a member of one Team only or a member of none)

So I have a nullable team_id field in Player table, referencing to a Team table.

But now I have to store an extra information about a player's position in the team. Like:

What is the right (normal) way to implement this?

My best ideas is an extra table:

Player one-to-one (NULL) PlayerTeam many-to-one (not NULL) Team

Upvotes: 1

Views: 325

Answers (2)

Tanzeeb Khalili
Tanzeeb Khalili

Reputation: 7344

If the player can only have one team, you can/should store that information directly on Player.

On the other hand, if you plan to store historical information, Player - Team might become a many-to-many, in which case the PlayerTeam join table would be the right place to put that information.

Upvotes: 0

Krycke
Krycke

Reputation: 3186

I would recommend your own suggestion making a new table:

id | player_id | team_id | role_id | number

and then make player_id unique, and team_id not null.

This will keep the player table clean and your database normalized.

Upvotes: 4

Related Questions