Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2003
    Location
    UK
    Posts
    1

    Question Stuck with database design. Please help

    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.

  2. #2
    Join Date
    Oct 2002
    Location
    Plymouth UK
    Posts
    116
    You should create the entities and relationships. Otherwise you have redundant data due to table that are not normalised.

  3. #3
    Join Date
    Jun 2003
    Location
    St. Louis, MO USA
    Posts
    8
    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!

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149
    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.

  6. #6
    Join Date
    Jun 2003
    Location
    St. Louis, MO USA
    Posts
    8
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •