Re: Semantics and Hierarchies

From: Alan <not.me_at_uhuh.rcn.com>
Date: Sat, 30 Oct 2004 13:18:44 GMT
Message-ID: <UwMgd.667$HT3.590_at_trndny08>


"Konstantinos" <noemail_at_noemail.net> wrote in message news: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:
>
> a) 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).
>
> b) 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).
>
> c) 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
>
>

Elmasri does cover this (at least in later editions). There are options presented, but the choice of which to use is up to the designer. Basically, you have the options Elmasri discuuses covered- you either have a table with a lot of null FK values (as in your example a) or you create separate tables and have an unfortunately complex structure as in your examples b & c. Any of the methods will work- it's just a matter of determining which is best to suit your application and reporting (input and output) needs. Received on Sat Oct 30 2004 - 15:18:44 CEST

Original text of this message