Home  |   French  |   About  |   Search  | mvps.org  

What's New
Table Of Contents
10 Commandments 

In Memoriam

Terms of Use

VB Petition

Tables: Sub-classing Entities

Rebecca Riordan

    When you’re creating a data model, you often have to deal with differing types of the same basic entity. For example, Customers might be companies or individuals, and you need to store different information for each. Products often have different types, as well. You probably don’t need to store the same information about a book as you do about a piece of software. Books might have the attributes Title, one or more Authors, a Publisher, a Publication Date, a List Price, and a Selling Price. Software, on the other hand, has a Version Number, but no Authors, and you may not care about the Publication Date.

    One solution to this problem is to include all of the attributes of all the types in a single table. At first glance, this seems a simple, straightforward solution. But it breaks down quickly as the system adds additional types, with additional fields. Also, the user interface can get ugly very quickly.

    A better solution is to borrow a technique from object-oriented design, and sub-class the entities. In relational terms, you set up multiple tables: a master table, which contains the information that is common to all entities, and subsidiary tables that contain the specific information for each type.

     Download OneToOne.zip

    The sample database provides an example of how to implement such a model. The primary entity is Party, which may be either an Individual or an Organization. The Party entity contains the fields common to all types, while the two subsidiary tables contain only the information specific to the types that they model. (The PartyType table is only used to populate a combobox in the sample form.)

    Note that the primary key (I’ve used an autonumber in the example) is assigned in the Party table, not the subsidiary tables. This is a common mistake, and makes programming far more complex than it needs to be. There is no need for the types to have additional identifiers, and in fact life is easier if they don’t.

    The sample form, also called Party, demonstrates one way to handle the user interface. There are two overlapping subforms, one for each type. When the user chooses a type from the PartyTypeID combobox, the appropriate form is made visible. In the sample, this happens in the OnExit event of the combo box. This code is duplicated in the OnCurrent event of the form, in order to display the appropriate subform as the user moves through the table.

    The sample query AllInOne, demonstrates joining all the tables together using left outer joins. You don’t often need a "grab bag" query like this, but it can sometimes be useful in situations where you prefer not to use subforms or subreports.

1998-2010, Dev Ashish & Arvin Meyer, All rights reserved. Optimized for Microsoft Internet Explorer