Hi all!
Here's a question for those wiser in the database design than I...is there ever justification in bending the rules with regards to First Normal Form?
Here's the story that led me to wondering about this...
THE SITUATION
I'm designing PHP/MySQL application to help those who look after people with cancer.
The aim is to collect information about what they needed care for. The hope is to build up information over time to inform decisions to improve their care.
FORM ENTRY
I used one of those "code generators" to build the a PHP form to enter the information required via listboxes & checkboxes. This is the (truncated) database it spat out:
Table:admission_details
--------------------------------------
#idadmission_details (primary key)
+patient_dob (date)
+idconsultant (foreign key to the consultant table)
+date_admission (date)
+date_discharge (date)
+site_appendix (TINYINT)
+site_caecum (TINYINT)
+site_ascending_colon (TINYINT)
+site_hepatic_flexure (TINYINT)
....etc...THE IDEA
So you can see... aside from having a primary key field in the 1st field, fields 3-4 collects some other basic data.
From Field 6 onwards, we're collecting information on "Where the cancer was" (via the site_* fields). Specifically, at the "PHP front end", these fields are checkboxes. If there was a cancer in say the appendix, it would be stored as "1" in the database. If not, the field will be NULL.
You therefore can have multiple fields with "1" if you have multiple sites with cancer. If you have no sites at all with cancer, all site_* fields will have NULL in them.
THE PROBLEM
The problem I can see in the database design is the site_* recurring fields - this is in voilation of The First Normal Form right? Thing is, if I try to separate this out into a separate "Site" table, it breaks the nice single entry form you get with the schema described above.
THE SOLUTION?
There's only so many bits to the human body. So whilst the site_* fields do recur, there won't be any more (or not much) additional site_* columns required in the database design. That being the case, whilst it isn't strictly in the first normal form, would this database still be acceptable in terms of efficiency of operation ie: search & retrieval? Or if I stick with this, will I be asking for trouble when MySQL trolls through thousands of those records querying people who had site_appendix = 1 and site_caecum IS NULL etc?
Looking forward to hearing your insight into this.
Thanks!
Kev