0

Is it good to separate address table for patient and emergency contact person?

Currently this are my tables

Patients

id

first_name

last_name

status

gender

PatientAddresses

id

patient_id

address_line_1

address_line_2

address_line_3

city

postal_code

state

country

EmergencyContactPersons

id

patient_id

first_name

last_name

EmergencyContactPersonAddresses

id

emergency_contact_person_id

address_line_1

address_line_2

address_line_3

city

postal_code

state

country

OR THIS IS BETTER?

Addresses

id

address_line_1

address_line_2

address_line_3

city

postal_code

state

country

EmergencyContactPersonAddresses

id

emergency_contact_person_id

address_id

PatientAddresses

id

patient_id

address_id

3 Answers 3

1

The second approach is much better. One massive advantage is that both patient and emergency contact could have the same address. Typically an emergency contact is a parent or spouse, so you could have a checkbox in your application that says "Emergency contact's address is same as Patient's". This would save on space in the database, add convenience for users of your application without any extra development effort from yourself.

0

It depends on your business requirements, there's no way to tell which design that is best without knowing the requirements. My guess is that both of these designs should work, unless you have some requirement that makes one of them unsuitable.

Some reflections, in all of your tables you have one attribute id. Example:

PatientAddresses
    *id
    patient_id
    address_line_1
    ...

if a patient can have more than one address, say home address, work address, etc, I would prefer an attribute that states what kind of address it is. I.e.:

PatientAddresses
    *patient_id
    *address_type
    address_line_1
    ...

Note that there is no id in PatientAddresses, the patient_id and the type of address is sufficient to uniquely identify an address.

Somewhat related is the naming of the attributes, I prefer to use the same name for an attribute throughout the model, i.e:

Patients
    *patient_id

I general I believe that attribute names like id, name, etc are to vague to be meaningful, it is an id, or a name of something in particular.

Personally I prefer to start the design process with a set of declarative statements describing the business I'm trying to model. Example:

  • A patient can have up to two addresses, one home address and one work address

Given a set of such requirements if is often easy to deduce a logical model.

Finally, these are just my thoughts on the subject, others will have different opinions.

4
  • "I general I believe that attribute names like id, name, etc are to vague to be meaningful, it is an id, or a name of something in particular". Not only that, but for debugging, it's a must. If you get an error message about a field named "id" - which f**&&^^ing table does it belong to? Using table_name_id is definitely the way to go. Same for giving your foreign keys meaningful names - the lovely Oracle error messages ".... constraint SYS0000004546 is being violated" are pretty tricky to debug! :-)
    – Vérace
    Jul 5, 2016 at 5:53
  • @Vérace, To clarify my intention, it's not strictly necessary to choose table_name_id as an attribute name. This might be the best name in some cases, but often there exists a concept in the business that can (and IMO should) be used for that attribute. Jul 5, 2016 at 6:03
  • 1
    Yes, of course, surrogate keys are not necessary, but they are usually sufficient - and most systems nowadays systematically use them. I know that purists such as Joe Celko and Fabian Pascal (dbdebunk.com - take a look) - and you :-), consider surrogates to be the invention of Beelzebub, and that natural keys are vastly superior. However, I think that boat has sailed in the "real world" - surrogates are the de-facto standard I'm afraid.
    – Vérace
    Jul 5, 2016 at 6:09
  • Haha, I would not consider me a purist in that regard. Choosing identifiers is part of a process, sometimes a surrogat key will be best, sometimes a set of natural attributes is to be preferred. Sadly though, this process is often not even invoked, and identifiers are chosen with a blindfold ;-) Jul 5, 2016 at 6:17
0

What you need to bear in mind is that database tables are about SETs. Without going into (what can be excruciating) mathematical detail, a SET is a collection of similar "things".

What constitues "similar" may depend on your particular application - maybe in some apps, males and females may be "similar" enough to be in the same table - say, stuff to do with grocery shopping in general - but if you were doing an app about cosmetics, say, it might be worthwhile splitting the genders?

This, IMHO, is clearly a case where the address table should contain the addresses of both patients and emergency_contacts. There is one interesting case where, say, spouses might well be each other's emergency_contact, and both might also be patients - say after a car crash.

What you should do there is to have two address entries for both - one as a patient and one as an emergency_contact, basically ignoring the fact that they are one and the same person at one and the same address.

Designing a database schema is essentially a balancing act between Occam's razor (Non sunt multiplicanda entia sine necessitate) and the principle outlined by Einstein that "Everything should be made as simple as possible, but not simpler".

The schema will then follow the lines you outlined:

CREATE TABLE patient
(
  patient_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  patient_name VARCHAR(50),
  ... other
  ... patient
  ... fields
);


CREATE TABLE emergency_contact
(
  emergency_contact INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  patient_id INT NOT NULL,
  ... other
  ... contact
  ... fields
  FOREIGN KEY fk_emergency_contact_patient (patient_id) REFERENCES patient (patient_id)
);



CREATE TABLE address
(
  address_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  patient_id INT NOT NULL,
  address1 VARCHAR(100),
  ... other
  ... address
  ... fields
  CONSTRAINT fk_address_patient (patient_id) REFERENCES patient (patient_id)
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.