Husband and wife to generate single records... until they seperate. Maybe.
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
2 Past Client
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?
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.