I have a parent table which contains demographics
and consent
data.
The parent table looks like as below
Person_id, Age, gender, Name, DOB, Consent_agreed, Agreed_share_phone_number, agreed_share_email
1 21 M FM , 10/1/90 N N N
2 23 F MF , 11/1/92 Y Y Y
I am planning to store the data like below
DEMO Table
Person_id, Age, gender, Name, DOB
111 21 M FM , 10/1/90
222 23 F MF , 11/1/92
CONSENT Table
Consent_Id Person_id Consent_agreed Phone_number email
1 111 N N N
2 222 Y Y Y
I have created a seperate table for consent because a person may update his consent over time and there might be multiple records.
Am I right to normalize it this way?
How should I decide whether to provide the consent_id
in the DEMO table
or not.
Should the DEMO table columns be like as below, with consent_id
indicating the latest consent status or consent status should not be included in demo table
Person_id, Age, gender, Name, DOB, consent_id
"a person may update his consent over time and there might be multiple records." Multiple consent rows for a person is definetly a reason to split the tables the way you've done it.
If an effective consent is always the latest row in consent
for a person (demographics
row) there is no reason to store consent_id
in demographics
.