normalization question

[Guest]

I have a table of measurements made on a number of birds. For each record
there are the measurements and the date, location and identity of the
measurer. On any given occasion one person will measure a number of birds in
the same place on the same day.

My question is: should I have a second 'Occasions' table containing unique
Date/Location/Measurer records that are then reated (one to many) to the main
Measurements table?

This seems to be required for normalization since there appears to be
dependency between the Date/location/Measurer fields if they are in the
Measurements table: for a given date and location most measurements will be
made by the same person. On the other hand the dependency isn't 100%: it's
quite possible to have a different measurer at the same location on the same
date, the same measurer at a different location on the same date etc etc.

The reason I'm hesitant about moving the Date/location/Measurer details to
their own table is that it makes the Table structure harder to understand for
the people who will actually be using it. From their point of view, it makes
more sense to have the cricial date/location/measurer details in the
measurements table rather than having to link to another table every time
they want to use the measurement data.

Am I right in thinking that I should have an Occasions table. And would it
be a heinous crime to ignore to make things more intuitive to the end users?

Thanks

ian

 

[Allen Browne]

Ian, do the occasions matter? That is, is anyone going to ask for any query
where the Occasion is important. For example, is anyone interested in
comparing the results of one occasion against another?

If so, then yes, it is important to track the occasions, and so the
intermediate table becomes important. If the occasions will always be
irrelevant, then you can do whatever is most efficient for you.

IMHO, it boils down to asking the right person the right question, so as to
come up with the simplest design that will handle everything they need. The
normalization rules provide umbrellas to live under, the data structure are
an artform, not a science.

 

[Guest]

Ian, do the occasions matter? That is, is anyone going to ask for any query
where the Occasion is important. For example, is anyone interested in
comparing the results of one occasion against another?

The Occasions per se don't matter. I was just thinking that the argument for
having a separate Table might be to avoid duplicating data. If at some point
it was discovered that the date had been entered incorrectly for an
'occasion', for example, it would only have to be altered in the occasions
table rather than for every bird measured on that occasion.

To be honest though iit intuitively makes more sense to me to alter
the date for every measurement rather than alter the single 'Occasions'
record. I think I may be rather tying myself in knots with normalization....!

If so, then yes, it is important to track the occasions, and so the
intermediate table becomes important. If the occasions will always be
irrelevant, then you can do whatever is most efficient for you.

So you'd suggest that because 'Occasions' in themselves aren't of any
interest, I should just include the Date/Location/Measurer information in the
Measurements table becuase that makes most intuitive sense to the end-users
(and me!)?

Thanks

Ian

 

[Allen Browne]

I can't really confirm that without understanding more about the data.

That may be best (since it's simplest), if it's adequate.

 

[Guest]

You specifically mention that you want users to be able to understand the
Table structure. This is unnecessary. Users should be interacting with the
tables through forms and reports, and should never see the tables.

There are two reasons for restricting users from seeing the tables. 1)
They could change something in the tables, which could prevent the forms and
reports from working, and 2) It is good job security for you to be the only
one who can fix problems that arise. As a side note: If the users do change
something, and break everything, you will have to fix it anyway. Always make
regular back-ups of the database, so if something does break, you can go to
the back-up, and not have to figure out how they broke it in the first place.

 

[Guest]

I completely agree that the tables should protected and interaction should be
via forms, and that's the way it's organised.

However, the end-users are scientists who require to manipulate the data to
generate their own specific datasets for analysis. There is no way that a
preprepared series of reports can anticipate their requirements. They
therefore have to understand where the data are stored and how the tables
should be linked so they can build their own queries in order to get the
required output.

Ian

 

[Guest]

You have them building their own queries? I'm impressed. Most of the
scientists I've worked with have trouble finding the 'on' switch for their
computers.

Seriously, though: It sounds as if they are working on front-ends, so that
the tables are merely linked to them from a server, and so are reasonably
safe. I only brought this up because I sometimes run into a user who wants
to create their own queries and reports, and they think they need to have
direct access to the tables to do that. It sounds like your users are a bit
more sophisticated about this than the ones I run into.

 

[Guest]

You have them building their own queries? I'm impressed. Most of the
scientists I've worked with have trouble finding the 'on' switch for their
computers.

I never said they were building them successfully.....!
As you say, they are working through a front-end on linked tables

ian