Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2010

    Husband and wife to generate single records... until they seperate. Maybe.

    Good evening,

    I am designing a database to track real estate transactions. I am maintaining seperate tables for contacts (people), categories (seller, buyer) and property. I'll show those structures below.

    I need to be able to show that John and Mary Williams both own 123 Main Street. They are past clients, when they purchased the home and are also prospective clients as they will be selling that same home shortly. As you will see below, no issue yet. My question surrounds what happens when I want to keep these two individuals as seperate contacts, since, in today's society, it's possible that they may not always remain together.

    Here's what I've got:

    pk_Con_ContactID Con_FirstName Con_LastName

    1 John Williams
    2 Mary Williams
    pk_Cat_Category Cat_Name

    1 Client
    2 Past Client
    3 Prospect
    pk_Rol_RoleID fk_RolesContactID_ContactContactID fk_RolesCategoryID_CategoryCategoryID

    1 1 2
    2 1 3
    3 2 2
    4 2 3

    The SQL statement is:
    SELECT Contact.Con_LastName, Contact.Con_FirstName, Category.Cat_Name
    FROM Contact INNER JOIN (Category INNER JOIN Role ON Category.pk_Cat_CategoryID = Role.fk_RolesCategoryID_CategoryCategoryID) ON Contact.pk_Con_ContactID = Role.fk_RolesContactID_ContactContactID
    ORDER BY Contact.Con_LastName;
    As you can imagine (or not, I'm probably complicating things), the output is:

    Williams Mary Prospect
    Williams Mary Past Client
    Williams John Prospect
    Williams John Past Client
    Here's where I need help: How should I link these two together so that I can produce a report that gives me ONE Prospect record for John and Mary Williams together (and any other prospects, like Paul Miller, etc)? I thought about adding a spousal ID field in the Contact table and/or some type of a bridge table, but what about folks who aren't married? And how do I show that John and Mary bought 123 Main Street when they were married six years ago, but now each is a seperate entity and has purchased their own house?


  2. #2
    Join Date
    Jul 2009
    Add a table called Group with Name (string), ValidFrom (date), ValidTo (date) columns.
    Add another table called GroupContact with GroupID, ContactID columns.
    Change the Role table to have GroupID instead of ContactID.

    Now you can build a Group that is a single person or a dozen. The group name could be John & Mary Williams or Acme Investments. The valid from/to dates tell you if the group is still active (they are still married) or if they have split up.

  3. #3
    Join Date
    Jan 2010
    Thank you Mark, that works perfectly!

Posting Permissions

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