37

Say, a table car has one-to-one relationship to tables electric_car, gas_car, and hybrid_car. If a car is electric_car, it can no longer appear in gas_car or a hybrid_car, etc.

Is there anything wrong with such design? Some problems that may occur down the road?

55

The different types of cars are an instance of a general problem that surfaces over and over again in data modeling. It is called "generalization/specialization" in ER modeling, and "superclass/subclass" in object modeling.

An object modeler uses the inheritance features built into the object model to solve the problem quite easily. The subclasses simply extend the superclass.

The relational modeler is faced with a problem. how to design the tables so as to emulate the benefits that one would get from inheritance?

The simplest technique is called single table inheritance. Data about all types of cars are grouped into a single table for cars. There is a column, car_type, that groups together all the cars of a single type. No car can belong to more than one type. If a column is irrelevant to, say, electric cars, it will be left NULL in the rows that pertain to electric cars.

This simple solution works well for the smaller and simpler cases. The presence of a lot of NULLs adds a tiny bit to storage overhead, and a little bit to retrieval overhead. The developer may have to learn SQL three-valued logic if boolean tests are done on nullable columns. This can be baffling at first, but one gets used to it.

There is another technique, called class table inheritance. In this design, there are separate tables for gas_car, electric_car, and hybrid_car, in addition to a combined table, car, for all of them. When you want all of the data about a specific kind of car, you join the car table with the appropriate specialized table. There are fewer NULLs in this design, but you do more joining. This technique works better in the larger and more complex cases.

There is a third technique called shared primary key. This technique is often used in conjunction with class table inheritance. The specialized tables for the subclasses have, as their primary key, a copy of the primary key of the corresponding entry in the car table. This id column can be declared to be both the primary key and a foreign key.

This involves a little extra programming when new cars are to be added, but it makes the joins simple, easy, and fast.

Superclasses and subclasses happen all the time in the real world. Don't be afraid. But do test your initial design for performance. If your first attempt is simple and sound, you'll be able to tweak it to speed it up.

  • 3
    Wow thank you! That's spot on what I was trying to figure out. Class table inheritance seems to be precisely what I need. I changed my accepted answer to this for future readers as I think it covers the question completely, not just my case. – Arthur Tarasov Dec 18 '17 at 13:52
  • 6
    Excellent Answer here. One tip: Thoroughly document these design decisions. Whichever route you take, it will not be obvious when someone is examining the database structure. Some databases such as Postgres enable you to bind a comment along with the meta-data of your columns, tables, and such. – Basil Bourque Dec 18 '17 at 23:05
  • You don't address the restriction on keeping electric cars from also being hybrid cars. You need a seperate table for that. – jmoreno Dec 19 '17 at 10:30
  • 2
    You are right. If you add a car_type field to the cars table, you can limit cars to belonging to only only one type, at the expense of deviating from full normalization. A good DBMS will let you define a check constraint that will prevent a car from being entered in more than one specialized table. There is some overhead in that, you go to add new cars. – Walter Mitty Dec 19 '17 at 11:25
  • @WalterMitty but without a car_type field, how would you know which table to look for for details when retrieving data? Do you have to read all three tables to see which one has data about that specific car record? – Josh Part Dec 20 '17 at 3:51
12

There is nothing wrong with having as many entity sub-types in your model as are needed to reflect the reality of the data that you're trying to model. The question isn't whether sub-types are a bad practice. The issue may be is it a good model?

For example, under your example, what do you do with something like an Audi A4 eTron - which is a plug-in hybrid? Is that an "electric car" or is it a "hybrid car"?

The other question you have to ask yourself is why you're sub-typing at all? How many distinct predicates do you have in your sub-types? Are any of these predicates shared between sub-types? The situation could get complicated.

Sub-typing isn't used in database design for classification. You can do classification with codes, foreign keys to code tables, or with flags. Sub-typing is used to model distinct predicate sets for different types of a thing of interest. If you're using sub-types merely for classification then that's a bad practice.

If your sub-types clearly and unambiguously model different predicate sets for the things your database cares about, then it's a perfectly good practice, regardless of how many sub-types you need.

  • Thanks, I was afraid I was setting some sort of a trap for myself. My problem is that each one of the subtypes will have a lot of columns. Some will overlap and I will put them in a car table, but many won't and will be put in a subtype table. For example, it will be something like storing elementary parts of car types. Electric car engine can have like a 100 parts, gas car engine 75 parts, and a hybrid 125 parts. 50 parts would be common and stored in cars, while 50, 25, and 75 will be in electric_car, gas_car, and hybrid_car tables – Arthur Tarasov Dec 18 '17 at 12:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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