In message <1141227436.798705.126830@z34g2000cwc.googlegroups .com>,
brianc@rstenstrom.com writes[color=blue]
>I have a table that holds the employee id, a job name and then 5
>columns (Proposal Phase Rating, Preconstruction Phase Rating, etc.)
>that hold a number rating 1-5. I've read that this design may not be
>considered "normalized" due to the fact that I will need to get an
>average (Job Average) across the columns. My question(s) is:[/color]
This is a bit of database theory that covers normalisation (I'm in the
UK, I use UK spelling.) Forget that you are using Access for a while,
because Access 12 might have new features.
You've done the difficult part and realised that if you store the
average value in that table then it won't be normalised. The next thing
to decide is whether that really matters to you. Sometimes it's OK to
have denormalised data, as long as you recognise that and are prepared
for the consequences.
Normalisation can cause problems in any of the fundamental data
operations; add, modify, delete. If you understand what can happen, and
are prepared for it, denormalisation is OK. It usually requires some
extra programming to keep the dependent data synchronised.
In this particular instance a delete operation doesn't require any
special action because you can delete the individual and average data
simultaneously. They are all held in the same record, there's no risk.
Adding a record has a risk, you need to be sure that when the record is
created the average field is filled with the correct value. You also
need to consider pathological conditions. What is the average if one of
the contributing values is null? Is this an error? Should you use the
average of the values you have, or return a null average? It's your
data, you need to answer these questions before you commit to a database
design.
Amending a record has similar risks. If you amend one of the
contributing figures you have to be sure that the average is
recalculated and stored before anyone else can read the wrong data. Just
as when adding a record you need to consider what to do with null data.
How you resolve these issues depends on the database implementation and
the performance requirements.
You could store only the contributing figures and calculate the average
on the fly each time. You keep the database normalised but at the cost
of having to repeat the same calculation each time you read the data. If
the data is written and never modified this is wasteful. (If your
database supports them you could create a view (a Query in Access) and
include the average as a calculated field.)
If your database supports them you could use triggers to recalculate the
average and store it whenever a record is added. If it doesn't (The Jet
database in Access doesn't) you can write some middleware code and have
your applications talk to the middleware instead of talking direct to
the database.
[color=blue]
>
>1) Should I break this table down differently, having 1 column for
>EmpID, 1 column for Job Name, 1 column for "Rating Type" and 1 column
>for "Rating Score"? If I do alter the table to reflect that changes in
>the previous sentence, doesn't that provide for extra data entries (ie
>Job name and EmpID have to be entered in each time a phases score is
>entered rather than each time a new job is entered).
>
>2) Unrelated to the questions above, I have a table with more than 40
>columns called Emp_Main. This table holds 1 record for each employee
>and includes emergency contact info, benefit info, etc. There will be
>less than 300 records in this table. Is it better for me to break the
>table down so that Contact Info, Benefit Info, etc are their own table?
>Is it better to have 1 large table or many small ones?[/color]
If you have sensitive data like emergency contact data then you need to
keep it safe. That's probably easier if you have fewer tables. It's also
easier to extract the data your management need to have at home in case
the office burns down.
Database design is partly driven by the theoretical (conceptual &
logical) structure of the data. You need that before you decide what to
store. It's also driven by practicality, your choice of database
software and the purpose of the data. There are usually several
different ways of building the database from a logical structure.
Different structures optimise different things, database designers
should know what needs to be optimised in any particular situation.
--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author.
