Semantics and Hierarchies

From: Konstantinos <noemail_at_noemail.net>
Date: Sat, 30 Oct 2004 00:02:50 -0400
Message-ID: <10o64kihac67r26_at_corp.supernews.com>



A topic that I havnt seen addressed adequately is how to model concepts such as subclasses and categories (according to the Elmasri 1985 terminology) from an EER model to the physical model of a database. The guidelines available (even from highly recommended books) are useful but inadequate, as they only deal with toy examples and provide very generic advise. They typically only consider the special attributes that each subclass may have but dont talk much about the case of superclasses and subclasses participating in different relationships with other entities in the database.

I am often faced with scenarios such as the following simplified example:

A company database has classes Customers and Employees (which are people) and then has class Suppliers (which are companies or people). These are perceived to be the significant entities in the application domain. All three classes can have multiple Addresses, Phones, Websites. However, instances that are people, have more attributes than instances that are companies.
Also, Customers, Employees and Suppliers participate in different relations in the database.
Conceptually I can model the above using EER constructs. When it comes to conversion to logical (or physical model) I am not sure.

What I am tempted to do usually is:

  1. Create a parent table "Contact" that has relationship with entities
    "Address", "Phones" and "Websites" (each contact can be associated with many
    addresses, phones, and websites). The primary key of contact will be ContactPK (for example's sake). I find this to be better then separately creating multiple relationships between each of the subclasses and each of those entities (which will also generate a large number of NULL FKs).
  2. Generate children (in the context of inheritance) of table "Contact" which are: "Employees", "Customers" and "Suppliers". The correspondence between "Contact" and each of those 3 tables is 1-1. This is a disjoint and total specialization. The key in each of these tables is again ContactPK (same as in the parent table).
  3. Here comes the tricky part. Whereas Employees and Customers are people strictly, Suppliers can be either companies, or people (this, for the sake of the example again). When they are people, I want to store additional attributes, such as gender. The solution to me seems to be to create a new table "Person" and relate it to the "Employees", "Customers" and those instances of "Suppliers" that are people. This new table will only hold attributes specific to persons. Its primary key will be ContactPK again, carried over from the other tables.

So in the end, I have "Contact" related to "Websites", "Phones" and
"Addresses", I have "Suppliers", "Employees" and "Customers" each
participating in different relationships with other tables in the database and I have "Persons" to hold attributes that apply only to a person but not to a company.

Is this a database design that makes sense, or is it overcomplicated? Any suggestions, discussion is greatly welcomed.

Konstantinos Received on Sat Oct 30 2004 - 06:02:50 CEST

Original text of this message