0

I'm learning how to design databases, and i've been ask to create the table that will hold this form: Medical History I'm learning to use Django/Python i've already made the markup in HTML and CSS, but I don't think that making each question on the form an column would be the best approach. For example in the family history i've thought of making it a separate table, while in the review of systems i want to make each to be a set.

alexzaizar09
  • 490
  • 1
  • 4
  • 18

2 Answers2

1

A pragmatic approach is to define tables based on the following criteria:

1) easy to select data from them (not to obtain many JOINs or convoluted queries that require ORs or strings splitting)

2) easy to understand (each concept maps to one table)

=> usually, normalized structures do the trick here

Of course, above are challenged in high transactional environments (INSERTs, UPDATEs, DELETEs).

I would assume then your case has moderate INSERTs, but more SELECTs (reports).

For Family history section I would normalize everything:

DiseaseType

DiseaseTypeId
Code            -- use to separate from a name that can change in time
Name            -- breast cancer, colon cancer etc.

CollateralOption

CollateralOptionId
Code            -- I would put UNIQUE constraints on Codes and Names
Name            -- no, yes, father

FamilyHistory

FamilyHistoryId INT PK IDENTITY             -- this may be missing, but I prefer if I use an ORM
PatientId -> FK -> Patient
DiseaseTypeId -> FK -> DiseaseType
CollateralOptionId FK -> CollateralOption
Checked BIT                                 -- you may not define this and have records for Checked ones.
                                            -- having this may put some storage pressure
                                            -- but prevent some "stuffing" in the queries

These structures allow to easily COUNT number of patients with colon cancer cases in their family, for example.

Shortly put: if there is not serious reason against it, go for normalized structures.

Alexei - check Codidact
  • 22,016
  • 16
  • 145
  • 164
  • Thank you for your answer, yes i have follow the normalization rules, could you recommend any book or blog i can learn more about this, i think it's not as simple as it may look on the format. I have to take into account a lot of things – alexzaizar09 May 06 '16 at 04:59
  • I have found [this question](http://stackoverflow.com/questions/377375/a-beginners-guide-to-sql-database-design) and its answers to be exactly what you are looking for. – Alexei - check Codidact May 06 '16 at 05:13
0

I don't see any advantage to perform any design tricks on this data structure. Yes, making a boolean attribute of each of your checkboxes, and a string attribute of each of your free texts, will lead to a high number of attributes in one table. But this is just the logical structure of your data. All these attributes are dependent on the key, some person id, (or at least that's what I assume, as a medical layman). Also, I assume that they are independent of each other, i.e. not determined by some other combination of attributes. So they go to the same table. Putting them on several tables won't gain anything, but will force you to do lots of joins if you query on different types of attributes (like all patients whose mother had breast cancer and who now have breast lumps).

I don't know exactly what you mean by making sets of some attributes. Do you mean to have just one attribute, and encode the sequence of boolean values e.g. in one integer, like 5 for yes-no-yes? Again that's not worth the trouble, as it won't save any space or whatever, but will make queries more complicated.

If you are still in doubt, try to formulate the most frequent use cases for those data, which will probably be typical queries on combinations of these attributes. Then we might see whether a different structure would make your life easier.

TAM
  • 1,731
  • 13
  • 18