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:


The SQL statement is:
As you can imagine (or not, I'm probably complicating things), the output is:

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?

Thanks,
P

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.

Thank you Mark, that works perfectly!