Results 1 to 3 of 3

Thread: Design Issue

  1. #1
    Join Date
    Dec 2009
    Posts
    3

    Unanswered: Design Issue

    Hi, first post....

    Can some explain best practice for me in this situation?

    I have five Tables: Policies, PolicyContacts, People, Companies, Trusts.

    A PolicyContact can be a Person, Company or Trust.

    How would I design this? Should I create an umbrella table called Contacts which amalgamates the Person, Company and Trust tables and use this ID in the PolicyContacts Table? Should I write triggers to update the Contacts table when a Company/Person/Trust is added?

    IE:
    ContactsId, ContactTypeId, ContactId

    Where ContactTypeId specifies Person,Trust,or Company, and ContactId is the ID from the relevant table?

    Or am I missing something?

    Cheers,
    O

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    I would create the parent super-type table for contacts. See this example:
    A Universal Person and Organization Data Model

    Quote Originally Posted by oferns View Post
    Should I write triggers to update the Contacts table when a Company/Person/Trust is added?
    No, because Company, Person, Trust should all reference the parent table and you ought to have a foreign key constraint for that, which means the parent table must be populated first. Create a stored procedure to do it. You don't need a trigger.

  3. #3
    Join Date
    Dec 2009
    Posts
    3
    sweet. Thank you. I was having a crisis of confidence there.....

Posting Permissions

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