Re: multiple table design problem

Hi Georg,

On 12 Feb 2002 10:18:34 -0800, georg_gruber_at_web.de (Georg Gruber) wrote:

>> b) why do you want to store adress data in an independent relation? Is
>> a adress something with it's own identity? IMHO, design the objects as
>> you look at them in real life: a hotel has adress attributes, a guest
>> has adress attributes and they (the adresses) are not related to each
>> other. They are just dump attributes. So I would prefer version 3.
>
>hi Marc,
>
>I think you've got a point here...
>I also wanted to store the attributes with the hotel, guest, ...
>
>I want to do this because the idea is that a hotel, guest or personnel
>can have more than one contact information... so it can't be put into
>the same table. I think it's not a good solution to create
>hotel_contact, guest_contact subtables for every type...

ok, so I haven't understood your problem right. Sorry.

Several questions come to mind:

Is it possible, that a hotel's contact and a guest's contact have the same identity? And if yes, does that has any relevance to your application?

If a contact is specific to a hotel, if there are no contacts, that aren't related to any hotel/guest/personal, then I would suggest the approach:

hotel 1:n hotel-contact

guest 1:n guest-contact

etc.

From here you might (but don't have to) take a further step and fold the entities together:

thing 1:n thing-contact

where thing AND thing-contact have an attribute TYPE with possible values [hotel|guest|personal]

The idea of three entities hotel/guest/personal and only one child entity contact seems a bit clumpsy to me. How would you determine, in which table to search for the parent of a contact? How would you implement referential integrity? How would you prevent from contact without parent?

One more thought:

If you say, solution

thing 1:n thing-contact

doesn't work, because there are attributes only specific to hotels and other attributes only specific to guests, then subtyping comes to mind again:

CREATE TABLE thing
(thing_id PRIMARY KEY,
thing_type NOT NULL);

CREATE TABLE thing_contact
(contact_id PRIMARY KEY,
thing_id NOT NULL FOREIGN KEY,
thing_type NOT NULL,
street,
city,
...);

CREATE TABLE hotel
(thing_id PRIMARY KEY,
hotel_attrib_1,
...);

CREATE TABLE guest
(thing_id PRIMARY KEY,
guest_attrib_1,
...);

CREATE TABLE personal
(thing_id PRIMARY KEY,
personal_attrib_1,
...);

So regarding your first posting, the clue is not to add one more table, but two more tables!

>
>Another issue is that contacts are very important in this database for
>export to other programs and I think it's convient to have it all in
>one contact table where I just run a query like "SELECT * FROM contact
>WHERE contact_type = 2".
>
>What do you say about this approach?
>

Again,has a contact any relevance to the world outside your database? Has it only relevance in conjunction with fact: "contact a relates to hotel x, contact b relates to guest y, .."

regards
Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Tue Feb 12 2002 - 13:08:30 CST