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.
- 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.
- 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.
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 ?
age
, as it changes over time. Store date of birth instead.