HockeyRef, Bert,
This is a database design issue:
1) The system must assign each user with a uniqueID. Don't use the SSN, just in case, in the rare instance, the SSN changes. The UniqueID NEVER CHANGES no matter what changes about the patient.
Oh and while you are at it, add a PatientName History table also. This table is automatically populated using a TRIGGER, every time there is a change to the patient's name. It includes the name that was changed, the PatientID, and a datestamp for the change. This way no matter how many times Elizabeth Taylor gets married, you can find her under any name.
2) To tie people to each other, you need 3 tables:
a) 1 the User/Patient Table with the UID
b) the PatientRelationTable. This table usually has 4 columns which hold the
RelID - the PK for the row
PatientID1
PatientID2
RelationshipID - identifies the relationship between Patient1 and Patient2
3) RelationsResolver Table this table takes into account the two patient and their sex from the initial entry and resolves the reverse relationship.
Now you have to also design a view to automatically handle the reverse of the relationship for example:
If Patient1 is the man and the child of Patient2. That automatically makes Patient2 the
father of Patient1, and Patient1 the
son of patient2 You do not need to store that reverse relationship in the database, rather just construct a view that will generate that for you.
...just musing along!!!
