Verifying table structure...

[Jill]

Hi all,

I've worked with databases before and have the general concepts of
normalization, but of course when applying, it can become overwhelming. I'm
working on creating a pond database for work that will record data from pond
samples we collect. I want to "measure twice, cut once" and do as best as I
can with the design of this database from the beginning...

These are some of the tables I have:

t_PondList (primary field of PondID, non-key fields of PondName,
PondLocation, SampleFreq, GeogrArea, TestType, and SampleLocation)

t_SamplerList (primary field of SamplerID, non-key fields of FirstName,
LastName, MiddleInit)

t_PondData (primary field of DataID), non-key fields of:
PondID (linked to t_PondList, will display PondName on form)
SampleDate
SampleTime
GrabLocation
Sampler1 (linked to t_SamplerList)
Sampler2 (linked to t_SamplerList)
MaxDepth (number entry)
SecchiDepth (number entry)
WaterColor (will link look up to table via data entry form)
WaterClarity (will link to look up table via data entry form)
IceCover (will link to look up table via data entry form, for percent
coverage)OdorRating (will link to look up table via data entry form)
OdorDescr (will link to look up table via data entry form)
ScumCover (will link to look up table via data entry form, for percent
coverage)
Trash ("note" field)
Pesticide Appl ("note" field)
HazConditions ("note" field)
PondMgrContact ("note" field)
Discharges ("note" field)
Aerators (yes/no for whether they were on or off)
Notes (other notes not included in note fields above)
GroupNumber (number of groups of adults/children observed at pond)
AdultNumber (total number of adults observed at pond)
ChildNumber (total number of children observed at pond)

t_PondDepthData (primary field of DepthID, foreign key of DataID) and
non-key fields of:
Depth
Temperature
pH
Conductivity
Dissolved Oxygen

I also have tables similar to t_PondDepthData but that focus on Weather Data
and Biology Data, using the foreign key of DataID. So, the t_PondData focuses
on "general" observational data about the pond itself.

My questions are:
1) Is my t_PondData table too big/too much data in fields? How can I split
further?

Right now, I have the sample date and PondID grouped together as an unique
index, so basically the DataID is unique for each PondID and Date. I'm
wondering if I need to split the sample date and time out into another table
with the PondID? Each pond can have more than one sample date and time (but
will not be sampled twice in the same day), and more than one pond will have
the same sample date...

2) When I create data entry forms, how can I automate entry of the DataID
from the form containing entry fields from t_PondData to the succeeding forms
containing entry fields from t_PondDepthData, t_PondWeatherData, and
t_PondBiologyData?

3) Is it best to create data entry forms separate from editing/reviewing
forms? Or is there an "easy" way to accomplish both in the same form? I wish
to minimize/prevent human error in changing data, most importantly the pond
name in the form associated with t_PondData to which all other data in this
table and succeeding table are related to... I thought of applying macros in
edit mode to verify the user wishes to make changes.

That's all and hopefully that's not too much to address!

Thank you,

Jill

 

[Philip Herlihy]

I wish I had the hour it would take to do your well-constructed question
justice! I don't - so here are a few observations:

There's no real upper limit to the number of fields in a table provided they
are all "dependent" on the key, and only the key. So, without painstaking
analysis I've wondered whey t_PondList has both a PondLocation and a
SampleLocation. I'd have thought SampleLocation was a property of a Sample,
not a Pond. There's also a GrabLocation in t_PondData.

Similarly, when you see two similar fields (Sampler1, Sampler2) that
suggests to me that you should have two records (think vertically!) instead
of two fields (horizontal thinking).

I believe (but watch out for corrections) that the most common relationships
are one-to-many and many-to-many. The former involves (as you clearly
understand) adding a foreign key in one of the tables (on the "many" side).
The latter involves an extra table where you can add multiple records
linking keys from two other tables. I tend to approach a database design by
looking for those relationships, while keeping an eye out for one-to-one
relationships which seem to me to be less common (again - watch out for
varying opinions on this).

I suspect your t_PondData isn't quite coherent. MaxDepth won't vary (will
it?) but SampleDate/Time will. That suggests to me that you have two
entities confused. The same goes for PondMgrContact.

I wouldn't base your design on the axiom that a pond won't be sample twice
in one day. Tempting fate!

I won't comment on forms - it's VITAL (yes, I'm SHOUTING!) to get your
tables right first. Everything else will fall into place once that's done.

Out of time now. Pity. Interesting application and a very well-phrased
question, but I have a train to meet!



Phil, London

 

[Jill]

For the sake of sating your curiosity for all your very valid observations...

I don't have a sample table because really, in our lab world, the pond
location defines our sample. The field names I've given are perhaps weak and
misleading. SampleLocation is what we assign what kind of sample we'll grab
from the pond - that is, whether we are to put a boat out on the pond or just
grab it from the shore. This remains fixed until management decides to
change our sampling program and add more ponds, for which we don't have
enough time to put a boat out on all of them!

Again, the GrabLocation is similar - boat or shore, but it's based on the
conditions of the sampling day. The pond could be frozen over and we don't
do ice-fishing, so a shore sample will have to suffice.

With the clarification of these fields, do they still stand the test?

As for the 2 samplers, we have 2 samplers out at the same time collecting
one set of pond data... if we have a row for each sampler, the rest of the
pond data will repeat twice for every pond/sample date... Or is there still
another way to do this in the vertical fashion?

MaxDepth does vary quite a bit for our shallow ponds dependent on rainfall,
snow, stormwater events, etc.

Hope you caught your train!

Thanks for your observations,

Jill

 

[Philip Herlihy]

You obviously have the necessary instinct for all this. You've produced a
design which is basically normalised but also "flexed" to reflect the "real
world". Getting that balance right can be tricky...

You'll acknowledge it can be hard to make sense of a table design simply
from the names of the fields involved. Without a lengthy dialogue I doubt
anyone (even one of the undoubted experts whom you'll also find here) could
give you a definitive critique. Having said that, here are a few more
comments:

As I understand it (doubtless imperfectly) you have a set of Ponds from
which a set of Samples (the name PondData is rather too vague, in my view)
are taken by a set of Observers. One Pond to many Samples; many Samples to
many Observers (two is enough to be "many"!). I'm assuming from what you
say that the two Observers only take one sample between them - that you
operate in "buddy pairs". If it's set in stone that there will never (not
even in five years' time) be a third Observer, then it does no harm to have
two foreign key fields in your Samples table, but if there could ever be a
need for flexibility, then you'd create an additional table which associated
(by key) records from the Samples table with records from the Observers
table. With this new table, which requires only two fields (the two keys)
you can have any number of Observers and use a Form/SubForm arrangement to
enter/display them. That's the classic "many-to-many" structure: an
"associative" table. Another example: say you had a table of Men and a
table of Women (you wouldn't, but bear with me in this crude example).
Since divorce and re-marriage is possible, you can't simply include a key
from one table in a record in the other. You'd need a table of Marriages,
which would contain a key from the Men, a key from the Women, possibly an
arbitrary primary key for the Marriage (e.g. an AutoNumber) and any
information which related to the Marriage such as the date of Marriage and
an optional date of dissolution. (So now we can all have 6 wives like Henry
VIII.)

Anything about a Pond which cannot change from Sample to Sample should be a
property of the Pond table. So, understanding that circumstances on the day
can mean you have to be expedient about where in the Pond you extract the
sample, if you have a field in the Pond table which describes Location it
might be called the PreferredLocation. The actual sampling location must be
a property of the Sample. A comparable project, sampling larger Ponds
(lakes?) might need to define specific Locations belonging (many-to-one) to
a given Pond - this would mean you'd need an additional table for Locations.

Where does MaxDepth fit in? The maximum of what set of observations? I
guess if you dip the water three times on each sample visit and record only
the largest, then MaxDepth is a property of the Sample, but if it's historic
(over many Samples) it must be a property of the Pond (just as I suspect
PondMgrContact should be), or a dynamic value calculated over many Samples.

Turning to your original Forms questions:

2) If you get the table structure optimised, then a Form/SubForm layout
will do some of the automation for you, if the Link Master Fields and Link
Child Fields properties are correct - as you start adding data in a subform
Access will add the foreign key (from the main form) as the relationship
dictates. So, you'd make the Depth, Weather and Biology forms subforms of
the Sample form. Access magic (but only available to those with
well-structured tables!).

3) There's no reason not to have multiple forms for different purposes
based on the same data. If you "split" your database (see Help) then you
don't even have to give the same interface to everyone. I'd envisage that
you'd select the Pond using a combo box, and you could use event-handlers on
controls to require confirmation (although users get used to that sort of
thing and tend to treat it as noise - when's the last time you read the
Terms and Conditions before ticking the box?). The default behaviour of a
form is to display existing records but to allow entry of a new one (unless
the underlying record source is not updateable for some reason). You can
set properties (e.g. Data Entry, Allow Edits) on the form at runtime to
control this behaviour, or you can have different versions of the same form
with those properties fixed at design-time. Alternatively, you can start a
form with a macro which includes "GoToRecord / New" for example.

That's about it from me on this, I think. - lots of trains this week. If
you need more, try picking out one specific at a time.

Phil

 

[Steve]

Jill,

I provide fee-based basic help for Access, Excel and Word applications. I
have provided numerous customers with a map of the tables for their Access
application. I design the structure of the tables and then create a map that
shows each table, the name of the table and all the fields in each table.
The map shows the general flow of information in the database. The map shows
the relationships that exist between the tables and the type of each
relationship. The map visually shows what forms are needed for data entry,
what forms can be created to display data and what reports can be created.
Finally the map visually shows what queries can be created for the forms and
reports. I can create a map of the tables for your database for a very
modest fee. If you are interested, contact me.

Steve
(e-mail address removed)

 

[Steve]

Jill,

I provide fee-based basic help for Access, Excel and Word applications. I
have provided numerous customers with a map of the tables for their Access
application. I design the structure of the tables and then create a map that
shows each table, the name of the table and all the fields in each table.
The map shows the general flow of information in the database. The map shows
the relationships that exist between the tables and the type of each
relationship. The map visually shows what forms are needed for data entry,
what forms can be created to display data and what reports can be created.
Finally the map visually shows what queries can be created for the forms and
reports. I can create a map of the tables for your database for a very
modest fee. If you are interested, contact me.

Steve
(e-mail address removed)

 

[Gina Whipp]

Jill,

In addition to FREE advice you have already gotten...

1. Be careful of your field names, ie. Depth. This is a Reserved Word in
Access and will be a problem for you as it is a problem when you you use it
as a field name. For a complete list see...

http://allenbrowne.com/AppIssueBadWord.html

This is one of the reasons I prefix my field names with the table upper case
letters.

tblPondList
plPondListID (PK - Use autonumber here)
plPondID (Use this to use your existing Pond Identification.)
plPondName
plPondLocation
plSampleFrequency
plGeogrArea
plTestType
plSampleLocation

tblSampler
sSamplerID (PK)
sFirstName
sLastName
sMiddleInit (not really needed as no one sorts on this, you can include in
FirstName field, ie. Sally J.)

tblPondData
pdPondDataID (PK)
pdPondID (FK)
pdPondSamplerID (FK)
pdSampleDate
pdSampleTime
pdGrabLocation
pdMaxDepth (number entry)
pdSecchiDepth (number entry)
pdWaterColorID (FK) (will link look up to table via data entry form)
pdWaterClarityID (FK) (will link to look up table via data entry form)
pdIceCoverID (FK) (will link to look up table via data entry form, for
percent coverage)OdorRating (will link to look up table via data entry form)
pdOdorID (FK) (will link to look up table via data entry form)
pdScumCoverID (FK) (will link to look up table via data entry form, for
percent coverage)
pdpdTrash ("note" field)
pdPesticide Appl ("note" field)
pdHazConditions ("note" field)
pdPondMgrContact ("note" field)
pdDischarges ("note" field)
pdAerators (yes/no for whether they were on or off)
pdNotes (other notes not included in note fields above)
pdGroupNumber (number of groups of adults/children observed at pond)
pdAdultNumber (total number of adults observed at pond)
pdChildNumber (total number of children observed at pond)

tblPondSampler
psPondSamplerID (PK)
psPondDataID (FK)
psSamplerID (FK)

tblPondDepthData
pddPondDepthDataID (PK)
pddPondDataID (FK)
pddDepth
pddTemperature
pddpH
pddConductivity
pddDissolved Oxygen

Suggestion, if your notes fields are going to be Memo Fields I would suggest
storing them in a seperate table with a link to the primary table. Every
once in awhile Meo Fields do not play nice with others and become corrupt.
I'd rather lose the notes then all my data. Of course, you could eliminate
this issue by just making your notes fields Text- 255 characters.

I also broke apart Pond Sampler as this is really a One-to-Many. I believe
Philip made reference to that.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[John... Visio MVP]

Jill,

I provide fee-based basic help for Access, Excel and Word applications. I
have provided numerous customers with a map of the tables for their Access
application. I design the structure of the tables and then create a map
that shows each table, the name of the table and all the fields in each
table. The map shows the general flow of information in the database. The
map shows the relationships that exist between the tables and the type of
each relationship. The map visually shows what forms are needed for data
entry, what forms can be created to display data and what reports can be
created. Finally the map visually shows what queries can be created for
the forms and reports. I can create a map of the tables for your database
for a very modest fee. If you are interested, contact me.

Steve
(e-mail address removed)

stevie is our pet troll who believes it is okay to annoy members of these
newsgroups by pretending to know what he is talking about and solicit
payment for work of questionalbe value.



These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

John... Visio MVP

 

[John... Visio MVP]

Jill,

I provide fee-based basic help for Access, Excel and Word applications. I
can create a map of the tables for your database for a very
modest fee. If you are interested, contact me.

Steve

stevie is our pet troll. Please do not feed the troll

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.


John... Visio MVP