Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2013
    Posts
    3

    Urgent question about relationships between tables

    Hi,

    I have two tables: Wife and Husband. I know it's a one-to-one relationship because each wife can only have one husband. But in my tables, there are cases that the couples have divorced. And if the wife stays single, the "Status" field in the wife table will be "divorced" and in the "Spouse" field I will have the ex husband's name.

    What is the best relationship to have between the two tables?

    Thank you!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Can't same sex couples get married?
    George
    Home | Blog

  3. #3
    Join Date
    Aug 2013
    Posts
    3
    Actually, my tables are named Member and Spouse. But to make my post as short as possible, i just siad Wife and Husband in my post.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    This:
    "each wife can only have one husband."

    Should this be this:
    "each wife can only have one current husband."

    If so, this is not a 1:1 relationship.
    George
    Home | Blog

  5. #5
    Join Date
    Aug 2013
    Posts
    3
    Correct, each wife can have one current husband. And if the wife doesn't remarry, she will not have a current husband. But I want to keep his info until the wife remarries.

    Right now my fields for Wife table are:
    - husband name
    - status (spouse, ex-spouse, single)

    So it's a many to one relationship?? (i'm confused now... )

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I would only have one table Persons unless you have different information (columns) about Members and Spouses. It is very, very rare that keeping two tables with exactly the same attributes is a good idea.

    I would then add another table to track relationships (marriages, civil unions, etc.) between persons. The Relationships table would include attributes like the initiator (presumably the husband) and the acceptor (presumably the wife) of the relationship, the dates that the relationship began and ended. When the end date is unknown, you can either leave it blank or set it to an "infinity" value.

    This allows you to track serial relationships (such as remarriage of a person after a divorce or being widowed), multiple relationships (such as polygamous marriages), etc.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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