Re: multiple table design problem

Hi Georg,

1. you may see it as a supertype/subtype kind of thing: the supertype is CONTACT, the subtypes are HOTEL, GUEST and PERSONAL. A CONTACT is either a HOTEL or a GUEST or a PERSONAL. Your tables would look like (quick'n'dirty)

Version 1 (fully normalized):

CREATE TABLE contact
(some_id PRIMARY KEY,
 contact_typ NOT NULL, <= (hotel,guest,personal) telephone,
 fax,
 ...);

CREATE TABLE hotel
(contact_id,
 further_attributes_specific_to_hotel);

CREATE TABLE guest
(contact_id,
 further_attributes_specific_to_guest);

CREATE TABLE personal
(contact_id,
 further_attributes_specific_to_personal);

Version 2 (all subtypes into one table):

CREATE TABLE contact
(some_id PRIMARY KEY,
 contact_typ NOT NULL, <= (hotel,guest,personal)  telephone,
 fax,
 ...,

 further_attributes_specific_to_hotel,
 further_attributes_specific_to_guest,
 further_attributes_specific_to_personal,

Version 3 (one table per subtype):

CREATE TABLE hotel
(some_id PRIMARY KEY,
 telephone,
 fax,
 ...,
 further_attributes_specific_to_hotel );

CREATE TABLE guest
(some_id PRIMARY KEY,
 telephone,
 fax,
 ...,
 further_attributes_specific_to_guest);

CREATE TABLE personal
(some_id PRIMARY KEY,
 telephone,
 fax,
 ...,
 further_attributes_specific_to_personal );

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.

On 7 Feb 2002 10:57:03 -0800, georg_gruber_at_web.de (Georg Gruber) wrote:

>hello,
>
>my name is Georg Gruber, i'm from Austria, Europe and this is my first
>posting to this newsgroup.
>
>i've tryed to find an answer for my question before, but i couldn't
>find any.
>
>so maybe you can help me...
>
>i have 3 table called HOTEL, GUEST, PERSONAL.
>all 3 tables contain the address information (telephone, fax, email,
>website etc.)
>
>so i want to make a 4th table called CONTACT.
>
>how can i transform this 3 tables to store all the contact related
>information in the CONTACT table? (i want a clean to provide a clean
>solution that uses 3NF)
>do i have to introduce a CONTACT_TYPE table?
>
>thanks for your time,
>comments welcome,
>Georg.

regards
Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Mon Feb 11 2002 - 19:28:20 CET