mysqldatabasedatabase-designentity-relationshiprelational-database

how to save marital relationship in a database


I have to save this information in a database

Person -> is married to -> Person

Where should I save that information? What is the proper design pattern should I apply here?

Thank you!


Solution

  • If you can only be maried to one person: 1:1

    -------------
    - Person    -
    -------------
    id (key)
    maried_to_id (foreign key)
    

    If you can be maried to more than one person or want to keep track of previous mariages, n:n

    -------------
    - Person    -
    -------------
    person_id (key)
    
    -------------
    - Mariage   -
    -------------
    first_person_id (foreign key)
    second_person_id (foreign key)
    start_date
    end_date
    

    (also first_person_id + second_person_id + date form a unique key for mariage. You could leave out the date, but then remariages wouldnt be tracked)