Re: multiple table design problem

hi Marc,

thanks for your thoughts and input.

> ======================================================
>
> 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.
>
Marc, your suggested approach is I think the best way to do it...

• relations ====

hotel 1:m hotel_contact
guest 1:m guest_contact
personnel 1:m personnel_contact

my previous approach tried to over-simplify the data model... I think the solution above makes more sense.

When there are different attributes in the contact this model is great.

In my experience the contact data is pretty much the same to all tables...

hotel_contact(id, hotel_foreign_key, name, address...)
guest_contact(id, guest_foreign_key, name, address...)
personnel_contact(id, personnel_foreign_key, name, address...)

So it would make sense to create a single contact(data) table.

> 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]
>
> ========================================================
do you think this approach with TWO type tables is a good one? i have doubts...

> 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!
>
> =================================================
yep, that's pretty much the solution i need!

the perfectly valid solution (hope so) to my approach is:

• relations ====

contacttype 1:m contact

contact 1:c hotel
contact 1:c guest
contact 1:c personnel

contact 1:m contactdata

• with this structure ====

contacttype (id, name)

contact (id, contacttype_foreign_key)

hotel (id, contact_foreign_key, ...)
guest (id, contact_foreign_key, ...)
personnel (id, contact_foreign_key, ...)

contactdata (id, contact_foreign_key, name, address, ...)

You'll notice that's pretty much the same solution as you lined out above... with a little difference in the TYPE / THING table.

Thanks for you posting,
Best Regards,

   Georg Gruber. Received on Thu Feb 14 2002 - 16:21:58 CET