|
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.
|
