Semantics and Hierarchies
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.
What I am tempted to do usually is:
So in the end, I have "Contact" related to "Websites", "Phones" and
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.
"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).
"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