0

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?

3
  • 2
    You need logic to determine the right table based on the type-column and that surely is a smell. But you know the right table from the start: archer. You do not want to know the amount of arrows a car-manufacturer has. The table human just holds data common to all types of humans (archers and car-manufacturers that is). Nov 8, 2016 at 15:01
  • Related question: stackoverflow.com/questions/3579079/…
    – user44761
    Nov 8, 2016 at 15:20
  • There are several related questions on dba.se. Some DBMS have support built in. Nov 8, 2016 at 16:45

3 Answers 3

3

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  |
4
  • @OddDev This is the idiomatic way. And your query would be: SELECT "Arrows" FROM "HUMAN" LEFT JOIN "ARCHER" ON "HUMAN"."ID" = "ARCHER"."HUMAN_ID" WHERE "HUMAN"."Forename" = 'John' AND "HUMAN"."Surname" = 'Doe' Nov 8, 2016 at 14:54
  • OddDev, you are not supposed to do such a thing. Your ORM in application layer should know in what table to search for attributes of the human with Type == 'Archer'. Or, for example, you can create Archers view on top of Human and Archer tables and search in it, not in Human table.
    – Vlad
    Nov 8, 2016 at 15:00
  • @MarcoBorchert Yes, of course I understand this. However, you don't know that you have to search for in the Archery table. You need logic to determine this. Like "IF type == 'Archer' THEN is ARCHER-table ELSE IF (...)". And that's a smell imho. Or isn't it?
    – OddDev
    Nov 8, 2016 at 15:02
  • @MarcoBorchert and Vlad I guess you've a point. Sorry... :) Let me think this trough once again.
    – OddDev
    Nov 8, 2016 at 15:03
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.

2
  • How do you decide if the approach is (still) reasonable? The devil on my shoulder says I only need that one table "object" containing every possible field. Nov 8, 2016 at 15:32
  • do you agree with the first blog entry you linked on it’s necessary to make (too) many (too) costly joins (right at the bottom) referencing the kind of solution @Vlad described in his answer? From my (limited) understanding that is exactly what an RDBMS is used for: specifying relations; There is not even a need for additional indices since all joins use primary keys only. Nov 8, 2016 at 15:46
0

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

2
  • Sounds quite interesting. Do you know if there is something similar for Oracle available?
    – OddDev
    Nov 8, 2016 at 14:32
  • For Oracle I'd recommend to store as XML.
    – Vlad
    Nov 8, 2016 at 14:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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