Database Design for Inheritance

Question

In my project I have an object Player (attr example: pl_id, pl_data1) that is one of these types:

• Its GoalKeeper (attr example: gk_data1)
• OR Defender (attr example: df_data1)
• OR Forward (attr example: fw_data1)

In mapping this objects to database :

Option 1: Make a Table Player with columns (pl_id,pl_data1,gk_data1,df_data1,fw_data1)

In this option we have one record for any player. So if an player is a goalkeeper, the column gk_data1 will be filled and other column (df_data1 and fw_data1) will be null.

Option 2: make

• Table Player with columns: (pl_id, pl_data1, type)
• Table GoalKeeper with columns: (pl_id, gk_data1)
• Table Defender with columns: (pl_id, df_data1)
• Table Forward with columns: (pl_id, fw_data1)

and pl_id in child tables (GoalKeeper,Defender and Forward) links to Player.pl_id. relation from child tables to parent is one to one and from parent to childs is one to one_or_zero.

questions:

1. which is better?
2. is there any other (and better) options?
3. and finally what is best practice in this situation?

• gnat: Sharing your research helps everyone. Tell us what you've tried and why it didn’t meet your needs. This demonstrates that you’ve taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer. Also see How to Ask

Answer 1

Best choice depends on the specifics of the situation.

Your two options resemble two techniques known as "Single Table Inheritance" and "Class Table Inheritance". Click to view Martin Fowler's presentation of these concepts.

In SO, questions about inheritance and table design are grouped under two tags of the same name. In SE.DBA, there is a single tag called Subtypes.

Answer 2

None of the above

GoalKeeper, Defender, and Forward are roles, not subclasses/types of Player. A Player may be a Defender in one game, and a GoalKeeper in another.

Typically one would have a role-map table to assign players to roles. It is difficult to say precisely what to do without more details, particularly the behavioral and data difference between the roles vs the Player class.