We've a table which has a bunch of columns. Let's say it represents humans. Every human has a specific type: archer-human, car-human and so on. Everytime we create a row nearly all of the fields are empty since we create a human of one type only. For example: | ID | Forenamne | Surname | Arrows | Bow      | Car-Manufacturer | Car-Color | ....| 0  | John      | Doe     | 5      | Compound | null             | null      || 1  | Peter     | Smith   | null   | null     | Toyota           | Blue      |As you can see there are already two null values and there are only two groups with two values each yet (in reality this adds up to about 20 * 30).We've discussed several practices:a) We create tables with master data: archer-humans, car-humansThis doesn't work since you would have a foreign key in the humans table and can't really tell whether you need to link to archer-humans or car-humans. b) We create tables with master data: archer-humans, car-humans and add a foreign key for each.Example:| ID | Forenamne | Surname | Archer-ID | Car-ID | ....| 0  | John      | Doe     | 13        | null   | | 1  | Peter     | Smith   | null      | 37     |This smells, doesn't it?c) We lay the foreign key into the master-data tables (as suggested by Vlad)Example:HUMAN| ID | Forename | Surname | Type   || 0  | John     | Doe     | Archer || 1  | Peter    | Smith   | Car    || 2  | Peter    | Doe     | null   |ARCHER| HUMAN_ID | Arrows | Bow      || 0        | 5      | Compound |CAR| HUMAN_ID | Manufacturer | Color || 1        | Toyota       | Blue  |This doesn't work since you can't determine the correct master-data table. The issue: how many arrows does John Doe have? can't be resolved: SELECT ???.ARROWS FROM HUMAN JOIN ??? ON HUMAN.ID LIKE ??? WHERE HUMAN.FORENAME LIKE 'JOHN' AND HUMAN.SURNAME LIKE 'DOE'You need logic to determine the right table based on the type-column and that surely is a smell. Any ideas how to split this table to avoid 100 of null-fields for each row?
How to split a table based on its child-types
database design;relational database
What's wrong with standard data normalization?HUMAN| ID | Forename | Surname | Type   || 0  | John     | Doe     | Archer || 1  | Peter    | Smith   | Car    || 2  | Peter    | Doe     | null   |ARCHER| HUMAN_ID | Arrows | Bow      || 0        | 5      | Compound |CAR| HUMAN_ID | Manufacturer | Color || 1        | Toyota       | Blue  |