0

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 ?

  • It doesn't make sense to store age, as it changes over time. Store date of birth instead. – mustaccio Apr 1 at 13:01
  • @mustaccio Good catch , but let's not stick to this particular example. It is used only as a means to the real question. – NickDelta Apr 1 at 13:14
  • It just shows that you haven't thought your model through. I'm not sure how "real" is your real question. "Being influenced by OOP", is "player_stats" an object in your model? Why? – mustaccio Apr 1 at 13:17
  • @mustaccio player_stats is a somehow different entity from the player itself. It describes the performance of a player not the player as a person. – NickDelta Apr 1 at 13:32
0

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.

| improve this answer | |
  • Amazing answer, gave me food for thought – NickDelta Apr 1 at 17:18
0

No. There's nothing unorganized having 50 columns of statistics, if they are normalized. But these columns may contain redundant information, if details are available concerning the player's career.

| improve this answer | |
  • Let's assume that there are details like percentages or mean values. How can we distinguish what is reduntant and what is not. – NickDelta Apr 1 at 12:50
  • If you can calculate them, they are redundant. You could still store them, for performance reasons. – Gerard H. Pille Apr 1 at 13:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.