By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,996 Members | 1,051 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,996 IT Pros & Developers. It's quick & easy.

Help with normalisation of table

P: n/a
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. tblRiskManagement) 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

Jan 3 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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. tblRiskManagement) 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********@FortuneJames.com

Jan 3 '07 #2

P: n/a
<CD********@FortuneJames.comwrote
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.
There's no distinction in Access between Option Buttons and Check Boxes.
Either can be used independently or as the clickable choices in an Option
Group. Whether one or the other is more intuitively independent or related
is a matter of personal perception.

Neither, by the way, can be resized larger. If you want a larger-size On-Off
choice, you can do it with a properly-sized Text Box with the font size that
you like, solid border of width to suit, with validation rule
In("X","",Null) limiting the values, and VBA in the Click event to set the
value depending on the current contents.

Larry Linson
Microsoft Access MVP
Jan 3 '07 #3

P: n/a
Larry Linson wrote:
<CD********@FortuneJames.comwrote
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.

There's no distinction in Access between Option Buttons and Check Boxes.
Either can be used independently or as the clickable choices in an Option
Group. Whether one or the other is more intuitively independent or related
is a matter of personal perception.

Neither, by the way, can be resized larger. If you want a larger-size On-Off
choice, you can do it with a properly-sized Text Box with the font size that
you like, solid border of width to suit, with validation rule
In("X","",Null) limiting the values, and VBA in the Click event to set the
value depending on the current contents.

Larry Linson
Microsoft Access MVP
Agreed. Let me restate. It is my personal perception that check boxes
are more intuitively independent than option buttons. It is also my
perception that most, but not all, windows applications adhere to this
idea. All people -- myself included -- are free to use option buttons
(or even toggle buttons) for that purpose anyway based on possibly
differing perceptions or philosophies or goals.

James A. Fortune
CD********@FortuneJames.com

Jan 3 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.