Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    1

    Angry Unanswered: Multiple Addresses Database Design

    Howdy All,

    I want to design a database that has a single table that holds addresses from several different entities. e.g

    Contacts
    --------------------------
    ContactID

    Sponsors
    --------------------------
    SponsorID

    Events
    --------------------------
    EventID

    Now a Contact can have multiple addresses, but only one is the preferred. My initial thought is to create lookup tables for each of the tables or just make the following table design for addresses
    addresses
    --------------------------
    AddressID EntityID Preferred(Bit Field) Street City Zip State....

    I want to maximize effeciency. Any ideas are greatly appreciated.

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Does an address have a separate existence from the entity joined to it?

    You could have the structure

    Address
    AddressID, .....

    EntityAddress
    EntityID, PreferredAddress, AddressID

    Entity
    EntityID, EntityType

    Sponser
    EntityID, ...

    Contact
    EntityID, ...

    Then you can show that several entities can have the same address.

  3. #3
    Join Date
    Oct 2003
    Location
    Jordan
    Posts
    28
    You can do the following for example

    Customer ID, Customer Name
    Contact ID, Customer ID (FK), Address Line 1, Address Line 2, Prefered
    Contact Address ID, Contact ID (FK), .............

    This will help you to make multiple addresses for each contact and multiple contacts for each customer.


    Regards,



    Firas arramli

  4. #4
    Join Date
    Sep 2003
    Posts
    522
    in order to minimize maintenance tasks you could have just 3 tables:

    EntityMaster (EntityID, EntityTypeID, etc.)
    EntityTypes (EntityTypeID, EntityDescription) -> static table
    Addresses (AddressID, EntityID, EntityTypeID, PrimaryAddressFlag, etc.)

    however, you need to keep in mind that by minimizing maintenance you may be stepping on your performance. on your first post you mentioned that you're after efficiency. by having a separate address table for contacts, sponsors, and events you'll achive faster performance vs. combining them all into addresses table.

Posting Permissions

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