Re: Semantics and Hierarchies

From: Konstantinos <noemail_at_noemail.net>
Date: Sat, 30 Oct 2004 15:43:22 -0400
Message-ID: <10o7ro3ke70bq2c_at_corp.supernews.com>


Thanks for the replies.
I ve been thinking on this the last 3-4 days. I have Elmasri's book and even though it seems to cover the options (4 of them) and some more considerations for union types the book doesnt really delve deep into the topic. There is another fellow, Hainaut, the main brains behind DB Main who also wrote a couple papers on this issue.

After further deliberation on this I came to a rather practical solution. It seems that I have a very detailed contacts database in the company database.
So what can be done is:
a) Create the contacts database independently. A "Contact" is the supertype and has subclasses "Person" and "Company", each with 1-N connections to Addresses, Phones etc.
This is a submodel within the larger database.

b) Create my main classes of interest in the application domain, such as "Suppliers", "Customers" and "Employers", and their relationships with other classes in the application domain database.

c) Relate each instance of "Suppliers", "Customers" and "Employers" with a instance from class "Contact".

Regards,
Konstantinos

"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
>
Received on Sat Oct 30 2004 - 21:43:22 CEST

Original text of this message