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.
subtypeand existing tag database-design? Your tag wiki sounds more like a canonical answer than a tag wiki.subtype-table-designis far too specific, even if your perception is that a majority of questions would involve table design.