Search code examples
sqlpostgresqldatabase-designforeign-keysdatabase-normalization

How to decide to have a foreign key or not


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   

Solution

  • "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.