Designing One-To-One Relationships

database-designrelationsschema

To begin with, I have to admit that I don't have great experience in designing database schemas. Being influenced by OOP , I find that by dividing the info we have about a object into different entities, we achieve better info organization.

For example , let's say we have a basketball player. For each player we have some general info like their name , their age , the position they play etc. We also have some info about their performance during his whole career (eg total points scored , total blocks etc). It is obvious that the relation between a player and their lifetime stats is 1:1.

Let's see some designing options for this relation now:

  • The most simple option would be to include the player's general info with their stats in a single table. But this seems to unorganized to me ; let's just imagine that we have more than 50 columns for the stats.

  • The second option would be to create a table player_stats and have as primary key the player_id and referencing through it the primary key of the players table. I think that is called One-To-One Bidirectional relationship.

One-To-One Undirectional

  • The third option would be to make put a foreign key in the player referncing his stats. That would be a One-To-One Undirectional relationship.

enter image description here

  • The fourth option is to make a join table combining both info. Placing unique constraints ensures that there will be no duplicates of a player or of a stat.

enter image description here

So let's summarize. Is it a good idea to break information to different tables? And if yes, what design should be preffered in One-To-One relationships and when ?

Best Answer

Option 1

This is the preferred design under most circumstances.

Option 2 & 3

From experience, try to stay away from splitting the data. I did it in one application but realized (after a few years) that it was doing more harm (programming wise) than good.

The only time this method makes sense is with an "IS A" type relationship design.

eg: The proper way to store a player's stats for a player like Michael Jordon would be to have BASKETBALL_STATS,BASEBALL_STATS, and GOLF_STATS tables. Each one "IS A" type of STATS.

unidirectional

For maintaining unidirectional, foreign key from the STATS table to the PLAYERS table is needed.

bidirectional

For bidirectional, you would also need an FK from the PLAYERS table to the STATS table that is validated at COMMIT time instead of DML time ( DEFERRABLE INITIALLY DEFERRED for Oracle).

This gets a little complicated when you have multiple types of Stats.

Option 4

I would use this design if and only if the STATS data is a Slowly Changing Dimension (SCD) Type 4 data. Under that design, you are actually storing the history of a player's stats as games/years progress.

EDIT As I think about it, this model doesn't even make sense for this scenario. Instead, you'd have a nullable FK in the PLAYERS table that points to the most recent row in the STATS table. You'll update that column with the correct information within the same transaction as the new stats information.

Related Question