Were the job of the modern database administrator and developer limited to coding SQL and ensuring good backups, we'd probably all sleep better and have more time to pursue such satisfying hobbies as Thursday night karaoke or hacking the OS of our Furbies. But alas, we must also implement efficient databases in the first place. This task is one of the least enviable in the database world because in no place can you do more good or cause more harm than in designing and implementing a database.
Though the task is daunting, time and experience bring a level of comfort. And you reach a point in your database-development learning curve when you're comfortable implementing many-to-many (M:N) relationships between tables. Sometimes you get so comfortable, in fact, that you overuse these relationships.
Although M:N relationships might intimidate you as a novice database developer, once you've worked with them a few times, they're relatively simple to identify, design, and implement. Usually at this point of mastery, the learning curve hits a brick wall. Few database developers and designers move beyond the "Big 3" relationships—one to one (1:1), one to many (1:M), and M:N—to discover the other types of relationships that can exist in a relational schema. People rarely explore, let alone master, the more obscure relationship types such as tertiary or bill of materials. However, these relationships are simply extensions of the three relationship types you've become so comfortable with. For example, a bill-of-materials relationship is nothing more than an entity that has M:N relationships with itself. In this relationship, a parts entity is made up of other parts, which in turn are made up of—you guessed it—yet more parts. But if you understand M:N relationships, understanding the bill-of-materials relationship isn't much of a stretch.
These less-common relationship types needn't be a mystery to your database schemas. To dispel some of the mystery surrounding these relationships, let's look at the underused and often improperly implemented supertype-subtype relationship, also known as a superclass-subclass relationship. If you've done any object-oriented development, you're already familiar with this relationship, in which several entities share some, but not all, attributes.
Note that in this article, I mainly cover the physical implementation of a supertype-subtype relationship, explaining the rationale for implementing this type of relationship and showing the dramatic performance gains that you can realize. For an in-depth discussion of modeling supertype-subtype relationships at the logical level, see Michelle Poolet's classic article "Supertypes and Subtypes," May 1999, InstantDoc ID 5226.
Rev Up Your Schema
You can see an example of the supertype-subtype relationship in various modes of transportation. Cars, trucks, and motorcycles are all types of vehicles—thus, cars, trucks, and motorcycles are all subtypes of the vehicle supertype. All types of vehicles possess certain attributes such as price, color, curb weight, and so on. However, other attributes, such as bed length, apply only to pickup trucks. Similarly, towing capacity is irrelevant to a motorcycle but a necessary attribute of cars and trucks. Using this example, Figure 1 shows a typical implementation of the supertype-subtype relationship.
In the schema that Figure 1 shows, I've represented the supertype and each subtype as a separate table, resulting in four database tables. The supertype table contains all the columns common to the subtypes, and each subtype table contains only columns specific to that type of vehicle. The NumberOfDoors column, for example, exists only in the Cars and Trucks subtype tables, while the Price column, which applies to all vehicle types, is in the Vehicles supertype table. The subtype tables also share a primary key with the supertype table. For example, in the Vehicles table, if a record with a primary key value of 10 is a truck, the associated record in the Trucks table also has a primary key value of 10. While the relationships between a supertype table and subtype tables aren't always 1:1 in a supertype-subtype implementation, in this example, each vehicle will always be of only one type, creating three 1:1 relationships.
Note that the Vehicles supertype table has a column named Type. This column is a subtype discriminator. It eliminates the need to perform an existence check on the subtype tables in order to retrieve detail information. If the Type column didn't exist, you'd need to check all three subtype tables to locate the record that has the same primary key as the supertype.
Prev. page
[1]
2
3
next page 
