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-humans
This 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?