Sathya V Sathya V - 3 months ago 10
MySQL Question

Two foreign keys reference one table and null able foreign key

I am new to Database tables and relationships .I need some help for the below requirements

Work flow

1. Hospital will have Male Patient
2. Hospital will have Female Patient
3. Hospital Will have Couple Patient but in RegTable it will stored as separate record for male and female.


For the above requirements i have designed the table structure below

Approach 1

RegTable

+-------+---------+---------+
| RegID | Name | Gender |
+-------+---------+---------+
| 1 | XXX | M |
| 2 | XXX | M |
| 3 | Husband | M |
| 4 | Wife | F |
+-------+---------+---------+


RegDetail

+----+------+-------+
| Id | FK_1 | FK_2 |
+----+------+-------+
| 1 | 1 | Null |
| 2 | 2 | Null |
| 3 | 3 | 4 |
+----+------+-------+


FK_1,FK_2 is RegId from Regtable

I have two questions


  1. Is my current approach is correct or Not ?

  2. Is alternative approach is there for the above work flow .



Kindly help me solve this . Thanks in Advance

Answer

You don't need 2 tables here.You can do it as shown below.

RegTables - this is the only table you need

Id int PK

Name string

Gender String 

PatientType tinyint 

Here you can maintain enum Type for separating Single and couple.

public enum PatientType : byte
    {
        Single=1,
        Couple =2,
    }

Update :

Treatments table

Id int PK

Name string

RegId int FK --> this is the foreign key referencing RegTables table