locked
Database design - multiple addresses RRS feed

  • Question

  • I have an Access database of medical providers that includes several relational tables for normalization purposes.  I have 3 practice addresses allowed for each record in my parent file, but many have more (such a laboratory with 100 locations).  I have to be able to include all address (and corresponding phone number) in website provider searches, directory listings, etc.  I'm not sure creating another table is the answer.  I don't want to create a new record for each address either.  This data has to remain very clean as it is also used for claim processing purposes.  All ideas are appreciated.

    Friday, July 8, 2011 10:49 PM

Answers

  • Expanding on Naomi's answer:

    My usual approach to contact detail modelling is as follows...

    1. I split out Party from PartyContactDetails, where Party could be Organization, Person, Client, Customer, etc...
    2. I subtype PartyContactDetails out to EmailAddress, PhysicalAddress, PhoneNumber, IMAddress, etc.  Each subtype record has its own set of identity valued surrogate keys
    3. I add a ContactDetailRole lookup table which is referenced by a foreign key in PartyContactDetails (ContactDetailRoles include home, branch, head office, mobile, personal, etc)
    4. I add a ContactDetailType lookup table which is referenced by another foreign key in PartyContactDetails, which determines which subtype table key is referenced.  e.g. If ContactDetailType = 'PhysicalAddress', the PartyContactDetailSubtypeID field would match the corresponding record in the PhysicalAddress table.

    Using this approach you get the following benefits:

    • This is a fully normalized data model
    • Unlimited contact records of any type against each Party record
    • The ability to apply role stereotypes to the Contact Details, which may lead to further extensions in functionality for contact management purposes (e.g. no calls to business addresses after 5pm, no calls to home phone numbers during proscribed times in jurisdictions where call centres are under restricted operating hours)
    • The ability to use a flexible contact field definition for each subtype of ContactDetails - e.g. Address can have Street Number, Street Name, PO Box Number, PostCode, TownCity, StateProvince, Country, etc... while PhoneNumber can be assigned Country Code, Area Code, Number and Extension.
    • Each field for each type of contact record can be given its own validation rules

    Writing GUI intput for a denormalized approach is simpler, but a more heavily normalized solution will always provide greater flexibility and the ability to capture more cases.

    Hope that helps.

     


    Jeremy Huppatz
    Managing Consultant: Solitaire Systems

    Tuesday, July 12, 2011 5:24 AM

All replies

  • If three practice capturing same information then you don't have to create separate transactional tables, one table would be sufficient with different addressID. other wise they are doing different functionality and capturing completely different data then you might need to create different tables.... in both cases address table you can still have one table with different ID...

    hope this helps you

     


    http://uk.linkedin.com/in/ramjaddu
    Sunday, July 10, 2011 1:02 PM
  • Check the database schema of [AdventureWorks] Database provided by Microsoft as a fictions/dummy database.

    Check these 2 tables and understand the relationship b/w them:

    HumanResources.Employee
    HumanResources.EmployeeAddress


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    Monday, July 11, 2011 7:30 AM
  • Assuming multiple providers can share the same address you can create one table that will have Address Info and the bridge table that will link Parent table with this Address table.

    If one Address can have multiple Phones (e.g. phone entry is separate from Address Info) you may also need to create a separate Phones table and again a bridge (linking) table.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, July 11, 2011 11:55 PM
    Answerer
  • Expanding on Naomi's answer:

    My usual approach to contact detail modelling is as follows...

    1. I split out Party from PartyContactDetails, where Party could be Organization, Person, Client, Customer, etc...
    2. I subtype PartyContactDetails out to EmailAddress, PhysicalAddress, PhoneNumber, IMAddress, etc.  Each subtype record has its own set of identity valued surrogate keys
    3. I add a ContactDetailRole lookup table which is referenced by a foreign key in PartyContactDetails (ContactDetailRoles include home, branch, head office, mobile, personal, etc)
    4. I add a ContactDetailType lookup table which is referenced by another foreign key in PartyContactDetails, which determines which subtype table key is referenced.  e.g. If ContactDetailType = 'PhysicalAddress', the PartyContactDetailSubtypeID field would match the corresponding record in the PhysicalAddress table.

    Using this approach you get the following benefits:

    • This is a fully normalized data model
    • Unlimited contact records of any type against each Party record
    • The ability to apply role stereotypes to the Contact Details, which may lead to further extensions in functionality for contact management purposes (e.g. no calls to business addresses after 5pm, no calls to home phone numbers during proscribed times in jurisdictions where call centres are under restricted operating hours)
    • The ability to use a flexible contact field definition for each subtype of ContactDetails - e.g. Address can have Street Number, Street Name, PO Box Number, PostCode, TownCity, StateProvince, Country, etc... while PhoneNumber can be assigned Country Code, Area Code, Number and Extension.
    • Each field for each type of contact record can be given its own validation rules

    Writing GUI intput for a denormalized approach is simpler, but a more heavily normalized solution will always provide greater flexibility and the ability to capture more cases.

    Hope that helps.

     


    Jeremy Huppatz
    Managing Consultant: Solitaire Systems

    Tuesday, July 12, 2011 5:24 AM
  • Thanks to all that took the time to respond.  I will be creating an additional address table.
    Wednesday, July 13, 2011 8:03 PM