Macbane wrote:
Hello All,
This has been bugging me for too long. I have a database that records
medical interventions. I am familiar with the theory behind
normalisation but am unsure what to do with the following data.
Firstly, I have a main table which records the specifics of the
intervention (date, reporter, details, location etc). It has links and
the database is normalised ok as far as I can make out. However, I
have 6 check box fields in the intervention table to record what I call
specific risk management outcomes (e.g. Was this serious, did it save
on staff time, did it save money etc) and are grouped together on the
form similar in style to an option group but obviously not one. I want
users to be able to tick each field as the feel appropriate without
restrcitions. What I am not sure of is if these field should be taken
out and put into another table (e.g. tblRiskManageme nt) as they are
part of a similar group and link that table to my main intervention
table. I have tried a few ways of doing this and they don't seem
right. I am also not sure if they need to come out of the table at
all! Obviously, I am not that familiar with the theory behind
normalisation! I hope this makes sense and someone can offer up some
advice.
Thanks in advance,
John
First, this kind of care in setting up the schema is rare and you are
to be commended for it. Second, the kind of functionality you are
talking about on the form is more conventionally represented by
checkboxes rather than option buttons since option buttons suggest the
possiblility of an exclusive relationship between the buttons. I
suggest that the checkbox choices stay in the main table with caveats.
This is more of a one-to-one data issue than a one-to-many issue so an
error here isn't as bad as duplicating data because of a lack of
normalization. A monolithic table will keep the database simpler
longer if the schema is expected to remain fairly stable. One
situation for those fields to go in a separate table is the expectation
of significant schema expansion in the RiskManagement direction. As
long as the choices remain independent, even tripling the number of
choices shouldn't cause concern. You may need to go to a separate form
for recording RiskManagement choices in the future. That may be a big
warning that the schema is about to expand. Also, with a monolithic
table you can have a conversion plan in place to normalize the
RiskManagement fields instantly if it looks like it is going in that
direction. Normalizaton queries are much simpler with one-to-one data.
Finally, be sure to look carefully at what kind of queries you are
going to need to produce your reports. Write the queries you will need
for both schemata before you even have any data so that you are aware
of any trade-offs. Also consider what happens if you get a huge number
of records. A separate table may have some advantages or disadvantages
in terms of efficiency. If you are comfortable writing queries with
one or more joins and don't have a huge number of records you can put
the fields in a separate one-to-one table right away. I have only used
one-to-one tables a couple of times, since the situations where they
seem appropriate, such as in your case, are relatively rare so others
may be able offer better advice regarding their use. As soon as you
create the one-to-one table expect someone to ask for a report with the
percentage of the records marked 'serious' at each location during the
past year. In other words, besides trying to get the best schema for
your expected needs, attempt to have as much flexibility as possible
for unexpected needs. I'm still leaning slightly toward the monolithic
table in your case. BTW, I hope you didn't use 'date' as an actual
field name.
I hope this helps,
James A. Fortune
CD********@Fort uneJames.com 
