Newbie - how to organize database/tables for scientific data

  • Thread starter Thread starter Elliott Alterman
  • Start date Start date
E

Elliott Alterman

I am supporting an environmental group capturing data on the health of
rivers and streams. Data are collected in various creeks (sometimes in more
than 1 location on a creek), by various people on a daily basis. Previously
data were added to an Excel spreadsheet. With growth in all aspects, I think
we should be building a common database to enforce standards of data entry
and facilitate data extraction for multiple purposes. The people who will
create and use the database(s) are not computer experts. I am reasonably
computer knowledgeable but NOT so in databases and MS Access in particular.
I have built a sample INPUT FORM to collect data and standardize data
formats. I have several questions.

Should this be one database?
A set of tables (1 for each creek or creek site)?
How do I learn about the easier ways to extract and manage data so I can
develop the tools and teach the grouip how to use the system?

Suggestions are appreciated.

TIA
 
your folks in the field are making notes of their activities? and someone in
your org has been entering the data into one or more spreadsheets "back at
the office"?
if so, i'd say yes, one database. no to "a set of tables for each creek or
site". you're thinking spreadsheets; Access stores and processes data very
differently.
you built an input form first? typical newbie mistake; you need to build
tables/relationships first - everything else in the database flows from
that.

on the surface of it, this sounds like a fairly simple db to build. but if
you don't know anything about Access, suggest your first move is to buy a
book to teach youself. Microsoft Access (version specific) Bible from IDG
Books is one good text, there are others. Once you get started, Access Help
can be very useful as well.
the learning curve in Access can be steep, depending on what you're trying
to do. this sounds like an important project where accurate data analysis is
key, so if you don't have time to learn the tool, suggest you bite the
bullet and hire an Access developer to write an application for you. in the
long run, it may be quicker, simpler and cheaper than building - and
fixing - one yourself.

good luck.
 
I see a minimum of three tables :
1) a Creek table with creekID(PrimaryID), creekName, any other creek info
you want to capture and use
2) a CreekSite table with creeksiteID(PrimaryID), creekID(foreignID),
creeksitename, maybe location
3) a DataCollection table with data fields from your form and a foreign id =
to the creeksiteID

From there, play with the forms wizard to create the necessary input forms.
That should get you started.

HTH
Damon

news:QWcec.5366$4Y2.1928@lakeread04...
 
Start learning about datbase normalization:

http://support.microsoft.com/default.aspx?scid=kb;en-us;100139

is a good start

Your problem may 'sound simple' but I just spent a week building just the
table structure for a 'simple' project and I've been working with databases
for over 30 years.

Example

Creek1
Site 1a
Site 1b
Site 1c
Creek2
Site 2a
Site 2b
Creek3
Site 3a

Tests Types
A -- recorded as text ('lots of sunfish', 'fishermen active')
B -- recorded as yes/no
C -- recorded as integer or a choice ("muddy" or "clear" or "turbid")
D -- recorded as a double (ph 6.1, flow 10.02, temp 69 Degrees)

Testers (people making observations)
Joe
Betty
Sam
Julie

Test Event
A set of test types conducted at one site by one tester on one
date/time.

One creek (of interest) has 1 to many sites
One site has 1 to many test events (tester, date/time tested, set of tests
conducted)
One test event has 1 to many tests conducted
One tester has 1 to many test events
One test has one and only one test event
One test event may have1 to many test types.

As others have said, get the data structure right (tables, relationships)
then the input
then the reports

Best of luck

By the way, if you are not currently collecting it add a latitude/longitude
(GPS coordinates) to the site attributes. This lets you do some GIS data
mining.

Ed Warren
 
I'm going to add just a bit more, and probably muddy the
waters a bit.

Having done alot of water/soil/air analysis tracking
systems, I come from a strong background in environmental
systems. The topic that will always come up is, "Is our
data geo-spatial?"

Are the people doing the water samples recording GPS
coords? If so, then the database could be done in one
table (well, 2 tables counting lab results).

tblSample
Latitude
Longitude
SampleID (PK)
SampleDate
Technician
Notes

tblResults
ResultID (PK)
SampleID (FK)
Constiuent
Contamination


If it is not Georeferenced every time, then are the
technicians pulling from set sites alongside the creek?
Then you could set up:


tblCreek
CreekID (PK)
CreekName

tblSites
SiteID (PK)
CreekID (FK)
Latitude
Longitude

tblSamples
SampleiD (PK)
SiteID (FK)

Rest is the same


As you can see, there are many ways to set up the database
depending on what data you are collecting, what you
already have, etc.

If you need help, email me at (e-mail address removed)


Chris Nebinger
 
Back
Top