Marriages: Past and Present

The original model includes a 1:1 relationship for current marriages. This prevents bigamy. That's good; it's the law in Texas. If John Jones (MID=12345) is currently married to Susan Smith (WID=98765), then the 12345 record in the table of Men will have a WID (foreign key) pointing to his "parent" spouse (98765). Since John's FK cannot simultaneously point to multiple parent records, John cannot have multiple current wives. Since the spousal relationship is defined as 1:1 instead of 1:N, no other record in the table of Men will be allowed to point to 98765. So, Susan cannot have multiple current husbands.

This assignment expands the original model to keep track of all former marriages. There is no law in Texas limiting the number of mistakes a person can make. So, we need a bridge table to represent this M:N relationship between men and women. The bridge has four columns/fields: MID, WID, MarriageDate and TerminationDate. The bridge has one row/record for each former marriage. If John Jones (MID=12345) has five former marriages, then there will be five records in the bridge for 12345. The WID field on these records indicates his five ex-wives. If Susan Smith (WID=98765) has no former marriages, then there will be no records in the bridge for 98765.

OK, can we just eliminate the 1:1 relationship for "current" marriages and use the M:N relationship to represent "current and former" marriages? Can we put six records in the bridge table for John (one current and five former) and one record in the table for Susan (one current and no former)? I'm sure your initial reaction is that it ought to work.

But, it won't. Why not? Think about it.

What if we have three records in the bridge for Will White (MID=99999)? One is for Beth Black (WID=11111), one for Gracie Gray (WID=22222) and one for Tammy Tan (WID=33333).

How can the M:N relationship force at least two of Will's three marriage records to be for "former" wives? What is preventing Will from having three "current" wives? What is preventing some other man in the table to also be currently married to Beth or Gracie or Tammy?

Bottom Line: The M:N bridge is in addition to the 1:1 relationship. It is not a replacement for it. We need to keep the 1:1 relationship in order to prevent bigamy.

More FAQs