CODEWITHSUNDEEP
mysqldatabasedatabase-design
ram914
ram914

Reputation: 331

MySQL : One to One Relationship Tables Merging

I am trying to simplify an application's database. In that database I have two tables let's say Patient and MedicalRecord. I know that two tables are said to be in One-to-One relationship iff that any given row from Table-A can have at most one row ine Table-B(It means there can be zero matchings).

But in my case, it is not at most, it is exactly. i.e., Every row in Patient should have exactly one row in MedicalRecord(no patient exist without a medical record).

Patient table has all personal details of the patient with his id as PK. MedicalRecord talbe has details like his blood-group, haemoglobin, bp etc with his id as both PK and FK to the Patient.

My Question is, can I merge those two tables and create one table like,

PatientDetails : personal_details and blood-group, haemoglobin, bp etc

Upvotes: 0

Views: 254

Answers (2)

Rick James
Rick James

Reputation: 142433

"bp" = "Blood pressure"? Then you must not combine the tables. Instead, it is 1:many -- each patient can have many sets of readings. It is very important to record and plot trends in the readings.

Put only truly constant values in the Patient -- name, birthdate (not age; compute that), sex, race (some races are more prone to certain diseases than others), not height/weight. Etc.

Sure, a patient may have a name change (marriage, legal action, etc), but that is an exception that does not affect the schema design, except to force you to use patient_id, not patient_name as a unique key.

Every patient must have a MedicalRecord? That is "business logic"; test it in the application; do not depend (in this case) on anything in the Database.

Both tables would have patient_id. Patients would have it as the PRIMARY KEY; MedicalRecord would haveINDEXed`. That's all it takes to have 1:many.

In situations where the tables are really 1:1 (optionally 1:0/1), I do recommend merging the table. (There are exceptions.)

Upvotes: 1

philipxy
philipxy

Reputation: 15158

If two tables have the same set of subrow values for a shared set of columns that is a superkey in both (SQL PRIMARY KEY or UNIQUE) then you can replace the two tables by their natural join. ("Natural join" is probably what you mean by "merge" but that is not a defined technical term.) Each original table will equal the projection of the join on that original's columns.

(1:1 means total on both sides, it does not mean 1:0-or-1, although most writing about cardinalities is sloppy & unclear.)

Upvotes: 1

Related Questions