5

Note: the "subtypes" tag has been created. Now let's see if it gets used. Revised proposal at end.

I am proposing a tag to be named "subtype-table-design". This would collect questions relating to this aspect of database design. Questions about how to design a type-subtype situation come up from time to time. A lot of new database designers have never seen any examples of how this is done, even though the situation comes up all the time.

An alternative, proposed in a comment below, is to name the new tag "subtype". This would collect the same class of questions, and it would also collect questions relating to the broader subject of subtype data management in general, such as queries relating to subtypes, or subtype data sharing between a relational database and an object oriented application. This alternative is quite attractive, because it may result in a more useful tag.

I've written a first draft for a canonical answer to questions that ask about table design for subtypes, and it's posted as an answer to this question. It's a pretty good first draft, but it needs a lot of work. I took the wiki summaries for three tags in SO and merged them into one. I'm not sure what the tag wiki for this new tag ought to look like.

The three tags in SO are single-table-inheritance, class-table-inheritance, and shared-primary-key. A problem with these three tags is they relate more to the techniques than to the underlying question. The single tag I've proposed is more likely to be found by people with a question about how to design for subtypes. The alternative proposal, a tag named "subtype" may be even better.

(Thanks to all the people who have contributed to the discussion. I have edited the question to reflect some of the commentary.)


I have edited an existing question to include the new tag, "subtypes". The edit is awaiting review before it becomes visible to others. I've written a proposed tag wiki for the subtypes tag. It's one of the answers below. Unlike my previous attempt, this proposed tag wiki is not a canonical answer to a subtypes question. At least I don't think so.

What do others think?

  • 2
    Since a lot of the questions will also (or only) revolve around querying and not just (or at all) around design (or querying only loosely involving the design), what is wrong with tagging such a question with tag subtype and existing tag database-design? Your tag wiki sounds more like a canonical answer than a tag wiki. – Aaron Bertrand Jul 2 '14 at 13:17
  • I agree with @Aaron, assuming there isn't too much ambiguity around the term "sub-type" (eg it's also used in OO features of some databases). The principle of what you are saying is good though, it is a recurring topic. – Jack Douglas Jul 2 '14 at 14:35
  • I agree that my tag wiki sounds more like a canonical answer than a tag wiki. Suggestions? – Walter Mitty Jul 3 '14 at 7:18
  • A lot of the questions that show up in this regard are specifically about how to design tables for a subtype situation, so the question of design is central to the question, at least in the questioner's mind. Where I think a single tag will help is to attract questioners to each other's questions. Unless, I'm missing something, "subtype" would be a new tag as well. Which is better as a new tag, subtype or subtype-table-design? – Walter Mitty Jul 3 '14 at 7:23
  • 3
    Build a question; then answer it with your answer below. That way it'll serve as a canonical answer that can be pointed to whenever one of these questions is posed. That might even get you a bunch of Internet Points™ – Max Vernon Jul 3 '14 at 19:17
  • My objection is not to new tags in general. It was that subtype-table-design is far too specific, even if your perception is that a majority of questions would involve table design. – Aaron Bertrand Jul 3 '14 at 21:27
  • I take your point, @Aaron. I may well come around to the same point of view. – Walter Mitty Jul 4 '14 at 13:39
  • I like the plan of building a question, and providing a canonical answer. Actually, that's what I have been doing, sort of, over in SO. What seemed unfortunate to me is that I ended up giving pretty much the same canonical answer to many different questions. Later on, I began keeping using one of the questions as a reference point, and pointing new questions back to it. – Walter Mitty Jul 4 '14 at 13:43
  • OK, here's a sample question, asked by somebody else, and a chance for me to plug in my canonical answer. dba.stackexchange.com/questions/69728/… – Walter Mitty Jul 4 '14 at 13:58
  • OK, so here's what I did: Because the asker is a beginner, I didn't provide the full canonical answer. Instead, I gave a cut down version of the canonical answer, tailored to the specific question, with just a hint that this sort of thing comes up all the time. – Walter Mitty Jul 4 '14 at 14:22
  • If a new question can be answered by an answer to an existing question, you shouldn't be answering the new question, you should be voting to close (or flagging if you don't have enough rep to vote) as a duplicate. – Aaron Bertrand Jul 4 '14 at 20:00
  • I did searches on "subtype" and "subclass" and came up with lots of hits. AFAIK, none of these has been closed as a duplicate. Why not? My guess is that the fact that they are duplicates is not apparent, unless the reviewer is used to looking for cases of type/subtype. The superficial details of each case is different from the others. But they are all the same pattern. I'm thinking that using one of these questions as the platform for the canonical answer is better than concocting my own question, which would be a duplicate anyway, at least by my standard. – Walter Mitty Jul 5 '14 at 15:24
7

Edit: The following has been categorized as a "canonical answer". I accept this categorization, and withdraw it as a proposed tag wiki. It's unclear when a canonical answer is worth more than an answer that's tailored to the particular question that was asked.

Subtype Table Design

There are lots of cases in database design where it is useful to model the subjects in the subject matter into types and subtypes. For example, cars and trucks are both vehicles. Some features pertain to vehicles, no matter what type, like Vehicle Identification Number (VIN). Some features might pertain to only a truck, like flatbed capacity.

Questions often come up concerning what the best way to design tables to reflect this situation. This tag collects those questions together.

In ER modeling, the type/subtype situation is often referred to as "generalization/specialization" (gen-spec). ER diagramming has a way of illustrating gen-spec in the diagram, but this doesn't address the question of what the table design ought to look like.

In object modeling, this situation is often referred to as "class/subclass". The concept of inheritance is closely associated with the subclass situation, and inheritance is one of the central concepts of both object modeling and object oriented programming. Languages like Java encourage the definition of classes that extend other classes. A lot of the grunt work needed to make this useful and functional is done behind the scenes by the Java compiler and the Java runtime system.

Unfortunately, traditional SQL has no built in mechanism for expressing the gen-spec relationship, and no built in design tools to help implementation. Worse yet, most people who learn how to build simple relational databases never get exposed to design techniques than can help. Hence the questions that appear here in the DBA area.

There are three design techniques that may help: Single table inheritance, Class Table inheritance, and shared primary key.

Single Table Inheritance

In this design, there is a single table that contains all of the data that pertains to the (super) class, and all the data that pertains to only one of the subclasses. In the vehicles example, there would be a column for "flatbed capacity". In rows where a given item does not pertain, the item is left blank (SQL NULL). The ID field for the vehicle is used to identify a vehicle regardless of what type of vehicle it is. These is usually one extra column, to indicate the subtype of vehicle for the current row.

This design has the advantage of simplicity. It's very easy to manage the data. There are two disadvantages to a table with a lot of NULLS in it. One is inefficiency in both storage and retrieval. The other is the confusion that can result when columns containing NULLS are used in selection criteria. SQL's three valued logic is often baffling to newcomers.

Class Table Inheritance

Class Table inheritance is an alternative to Single Table Inheritance. In this design, there is one table for the (super) class, and one table for each subclass. In our example, there would be three tables: Vehicle, Car, and Truck.

Data like VIN, which pertain to all vehicles, would go in the Vehicle table. It might even be the primary key. Data like FlatbedCapacity would go in the Truck table.

This has the advantage of making the Vehicle table smaller and faster. It has the disadvantage of requiring joins whenever a query require both super class data and subclass data. It isn't hard to come up with views that combine superclass data with subclass data for each subclass. It's even possible to come up with a union of all of these views into a single view that has the appearance of a single table inheritance design.

Often, but not always, there is a column in the super class table that indicates what sub type a given row belongs to. An alternative is to simply require a join with the appropriate subclass table whenever selection is to be restricted to a single subtype.

Shared Primary Key.

Shared Primary Key is a useful technique to express one-to-one relationships. If implemented correctly it will enforce the one-to-one nature of the relationship. This technique is often used in conjunction with Class Table Inheritance, because subclass/class relationships are always one-to-one or zero-to-one.

In this technique, the subclass table does not have an ID column of its own. Instead, the primary key of the subclass table is a copy of the primary key of the corresponding row in the superclass table. The subclass key is not filled in by a DBMS autonumber feature. Instead, the application that adds to the superclass table and one of the subclass tables takes charge of propagating the primary key from the superclass table to the subclass table at insert time.

This technique has an additional advantage, beyond enforcing the one-to-one rule: It speeds up joins in most environments. SQL databases generally create indexes for primary keys, and the optimizer knows to use them to good effect when joins are needed. Still, joins are going to introduce some delay, although it may be a reasonable delay.

1

This is a proposed tag wiki for the subtypes tag, if it gets created.

Subtypes, often called subclasses, come up often in the course of processing data. Subtypes also come up often when managing data that is to be processed and/or to be retained in a database.

Object oriented processing environments accommodate subtypes fairly well. The inheritance mechanism is central to object oriented systems, and provides a natural way of managing subtypes. Relational systems are less well adapted to managing subtypes. Programmers, DBAs, and database builders often have to implement the infrastructure themselves.

Many of the people who ask about subtypes are experienced programmers who are proficient in OOP. They are often new to the kind of data management that DBAs do.

This tag is for questions about how to handle subtypes in a database environment. The questions might relate to subtype data definition, to updating subtype data, or to constructing queries involving subtype data.

0

Since the subtypes tag was created, there has been a remarkable drop off in the incidence of people asking this category of question. This may mean that the tag was not needed. On the other hand, it may mean that the tag is working. Maybe a lot of people visit the tag, find the two questions tagged there, and don't need to ask their question after all.

I have no idea how to figure out which it is.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .