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

[backendveloper]

This are the functionalities I'm trying to store in the db:

Birth Register –This should result in the entry of a new record for a person in the database
Marriage Register – This should allow for the entry of marriage records as well as divorce records
Death Register –This should allow for the entry of death records resulting in the update of the person record

I would like to know how I can be able to update the person table , when I enter a new birth record and death record

I'm using mysql database

Thank you in advance

[robert_volk]

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:

Family Relation Tables in SQL

You might also add start end datetime columns considering divorce rate these days. So a person can be married x times.

And some other folks chime in as well.

[backendveloper]

Thank you very much Sir I really appreciate your feedback very very much.

I'm tasked to create a national database at my internship as my first project.

I already coded it in java with cuba platform plugin in intellj I just want to have a simple db , I'm very much in love with databases so I would like to be a pro like you, someday. that makes sense and your feedback is very very nice and helpfull.

here are the full guidelines and instructions for the project:

National Identification System

The national identification system is a database of all people in the country. The database keeps a record of each person as follows:

• Identification Number –generated by the system
• Surname
• Name
• Gender
• Date of birth
• Date of death
• Citizenship
• Parent(mother)
• Parent(father)

The citizenship field has these possible values: Citizen, Resident, and Foreigner.

When displaying records of people, the following fields are required but should not be saved in the database:

• Age
• Spouse

The NIS must allow the user to capture existing people (without entering parents or spouse). The entry of parents for existing people is not required.

The NIS must allow for the entry of the following registers:

• Birth Register –This should result in the entry of a new record for a person in the database
• Marriage Register – This should allow for the entry of marriage records as well as divorce records
• Death Register –This should allow for the entry of death records resulting in the update of the person record

Generation of Identification numbers

• All persons born up until 1999 will have numeric ID numbers with the following pattern: YYMMDDXXXXZ where XXXX is a sequential number and Z is a check digit.
  • XXXX is assigned sequentially to persons born on the same day.
  • Z is a check digit according to this standard(don't worry about this sir)

For example if only two people were born on 12 April 1998, they will have the ID numbers: 0098041200015 and 0098041200022

All persons born from the year 2000 onwards will have numeric ID numbers with a similar pattern except the first two digits: YYYYMMDDXXXXZ. The 2056th person born on 15 January 2015 will have the ID number: 2015011520568

sir for This part just ignore, I coded the ID generation staff already and its working, just focus on what my db would look like

Registration of Birth

When registering a birth, the following constraints apply:

• The mother must have been alive at least one day before the birth of a child (This caters for the remote possibility of death shortly before birth, just before midnight and the subsequent delivery of the child just after midnight)
• The father must have been alive at least 12 months before the birth (There is a remote possibility that a pregnancy may last more than nine months)
• The father and mother must be at least 10 years old at the time of the birth
• The mother and father must be of the correct gender

Registration of Death

This will only change the date of death on the person’s record. The following constraints apply:

• A male may not be registered as dead if it will lead to his date of death being more than 12 months before the date of a birth registration as a father.
• A female may not be registered as dead if it will lead to her date of death being more than one day before the date of a birth registration as a mother.
• A person may not be registered as dead on a date earlier than an existing marriage or divorce record

Registration of Marriage

The following constraints apply:

• Both spouses must be 16 years or older on the date of marriage
• A divorce record must reference a specific marriage record and must not be earlier than the marriage record.
• Marriage records may not overlap (Perhaps a person can divorce one spouse and marry another on the same day or indeed divorce and re-marry the same person on the same day!)
• A marriage can only be between a man and a woman.

System Functionality

The NIS will provide the user with the following functionality:

• Related Persons –show all persons related to the selected person including:
  • Parents
  • Spouse
  • Children
  • Step-children
• Clan – Show the entire clan (descendants) of the selected person
• Relationship Query – What is the relationship (if any) between two selected persons?