How to split a table based on its child-types

Question

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?

Answer 1

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  |

Answer 2

What you're doing is one of the most reasonable ways of supporting polymorphism within the generic relational model.

One drawback is the lack of identification of the true type of the row, but you can add a string column that captures a name for the type.

Another drawback is that we can't enforce groupings of the optional columns - prevent usages of inappropriate columns for a type, or mandate all columns for a given type are present.

Still, believe it or not, this is a reasonable mapping of a class hierarchy. The best parts about this approach is that the id's are easy to manage, and, the queries are reasonable.

---

For more information on modeling inheritance in relational db's, see:

An illustration of three different ways (including the optional columns approach), each of which have some drawbacks (that I consider worse than those for what you are doing): http://www.vertabelo.com/blog/technical-articles/inheritance-in-a-relational-database

---

Another approach, Microsoft's Nordic Model, is a pretty big departure from standard relational model; it's almost like doing RDF in SQL.

Answer 3

I would store that information in an HSTORE column if using PostgreSQL https://www.postgresql.org/docs/current/static/hstore.html or JSON if using MySQL https://dev.mysql.com/doc/refman/5.7/en/json.html