Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012

    How to design tables with similar data yet different fields

    Hi - I'm struggling with a table/relationship design. For simplicity , let me illustrate with an absurd example.
    Let us assume:
    1. We want to store a table of people and a table of building addresses.
    2. Only one person can live in each building.
    3. Each country in the world uses a different address method.

    With regard to point number 3, each country uses a different address method, for example:
    1. One country may use City, Street, Number
    2. One country may use a postal code only
    3. One country may use GPS coordinates
    4. One country mmay use some sort of grid reference map
    5. You may add more countries later that have more schemes
    6. etc

    It would seem difficult to have one table for building addresses, because it would be filled with a bunch of fields that are unused by a lot of addresses.
    So I gather you would make many different address tables???? Such as:
    1. Sweden_addresses
    2. France_addresses
    3. China_addresses
    4. etc

    Firstly, is it correct to make a bunch of different address tables?
    Secondly, if you relate the people table to a single address table, it is simple (use one single foreign key). How do you relate the people table to an unknown number of address tables (and wouldn't subsequent processing be unwieldy)???

    Somebody must have come across something similar before.
    Please remember the above address thing is just an example (I understand real addresses don't work like that).

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Have a look at the sub super type datamodel
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2012
    Quote Originally Posted by healdem View Post
    Have a look at the sub super type datamodel
    Ah thanks, I didn't know it had a name. Now that I know what to seek, I can dig around for some info (already I see that it is controversial).

Posting Permissions

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