[maricar]

I am designing a 'contacts' database and now stuck with some design issues. The contacts database is to contain information on people and the religious ethnic and political group they belong to.

I can identify 'person' as an entity.

PERSON
person-id
fname
lname
address
religion (christianity, hinduism, islam++)
ethinicity (English, Asian, African++)
party (Labour, Libdem, Tory++)

I am not too sure whether 1) religion 2)ethnic group and 3)political party should be attributes or separate entities.

From a practical viewpoint, I can create three tables 'RELIGION' ,'ETHNICITY' and 'PARTY' and use them to feed appropriate fields in PERSON table (In MS Access) . This method removes the need to create relationship between the tables.

OR I can create the entities and set relationships between them and the 'PERSON' table as below:

PERSON
person-id
fname
lname
address
religion-id
ethinicity-id
party-id

RELIGION
rel-id
rel-name

ETHNICITY
eth-id
eth-name

PARTY
party-id
party-name


what's best to do. Please advise me.

[gannet]

You should create the entities and relationships. Otherwise you have redundant data due to table that are not normalised.

[ss5416]

I'd take the second model you had one step farther.

Your person table should just be facts about that person. The Id, name, address, etc. for a basic person table was right on. You may find that will be a little to small too once you look at multiple phone numbers or addresses, etc but that's another topic.

But pull out the other entity references into a fact table.
PersonDetails (PersonID, ReligionID, PoliticalID, etc.)

With your relationships in place, you'll be able to query exactly what you want. Until you run into multi-cultural people that is! But that too is another discussion.

Hope this helps!

[blindman]

I don't see any advantage in pulling 1 to 1 relationship data into a separate fact table unless your record length starts to get enormously long. It just means one more join in your queries. I once had to deal with an Access database application that consisted of 25 different tables all joined in 1 to 1 relationships. Blech!

Whether you store religion in the persons table as an ID field or the actual text field is a matter of personal preference, but do set up separate table for religion and the other attributes and establish relationships between them and the persons table. Eventually, you'll be glad you did.

My personal preference is to use a random autonumber id indexed for every table, but save the primary key for whatever the natural key of the data you are modeling is. This is because I have set up template forms and code for automatically linking forms and subforms, and this allows them all to work off the autonumber ID of whatever form calls them.

[sbaru]

I agree with Blindman. I don't see any good reason why you should split the contact table into 2. I would create a contact table, religion table, ethnicity and party. And have the ids of these other tables as columns in my contact table.

[ss5416]

Okay guys, you convinced me. Perhaps the 1:1 really wan't necessary. Even if you did expand into multi-cultural, the 1:1 wouldn't gain anything and would need to be broken down a little further.

Good Discussion!