Re: multiple table design problem

> ======================================================> > 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]> > ========================================================
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!