How to represent birth and death record in mysql db without duplicating data of an existing persons table existing persons table

2

First glance, not comprehensive:

  1. You're asking how to update the Person table when changing births, marriages, and deaths. The correct answer is: don't. That table should either serve as the only source for that information; or, for columns in Person that can be derived from, or otherwise duplicate data in the other tables, should be dropped or simply not used. Continue for more details.

  2. You have date of birth and date of death (I presume) in the Person table. The schema you posted for deathRecord would be completely redundant, just log the date of death in the Person table.

  3. BirthRecord is not entirely redundant, but name, surname, and DOB columns are, as they already exist in the Person table. Now, you can change them to BirthFirstName and BirthSurname, to differentiate them from people who change their names later. This however suggests a separate Name or NameChange table, which is beyond the scope of your question.

  4. BirthRecord wouldn't support or differentiate adopted children, foundlings, orphans, etc. where the birth parents are unknown. Same for stepchildren and stepparents.

  5. Similarly for gender and citizenship, they're already in Person table, but that can be changed as well (and may consider renaming to BirthGender and BirthCitizenship if you need to classify it correctly)

  6. Marriage table looks OK, but man/woman column names should be changed, Partner1/Partner2 would be better. This isn't just supporting same sex marriages, people can get married and not specify either of their genders. There are various kinds of marriages, civil unions, life partnerships, etc. Unless this is for a school project, and even then, this is not great terminology to use for your data.

  7. There are some additional data quality issues with the Marriage table, such as preventing same person married to 2 different people simultaneously...unless you need to support people married in the state of Utah. Also ensure that someone cannot marry themselves. You'd need to decide if a couple can get remarried after a divorce, think Elizabeth Taylor and Richard Burton.

  8. If you're going to have a Marriage table, then the spouse column in Person table is redundant, and filling it out while having a Marriage table will only lead to discrepancies. See below for foreign keys.

  9. For the life of me I cannot figure out what Trace_person is intended to do. At best it's 75% redundant to the other tables. Trying to keep these multiple sources in sync will make you want to murder someone.

  10. None of the tables in your diagram show any foreign keys between them (which probably won't matter as MySQL doesn't enforce them anyway, depending on which version you're using). Without that kind of relationship defined, you cannot hope to maintain your title goal of "How to represent birth and death record in mysql db without duplicating data of an existing persons table existing persons table"

  11. I'm really unclear about the "linestring" data type for name, surname, parents, etc. If you are thinking about representing relationships in a database using geometric data, and nothing else, I cannot stress strongly enough what a bad idea that is. Read the link below for some complications you'll encounter.

I have some comments on relationship table modeling here:

And some other folks chime in as well.

1 Like