Database for Scientific Data

[bymarce]

I have set up a database to hold data and test parameters. Currently I have
two main tables, one is a catalog of samples including a catalog
number(primary key), description, location, ... My other table has all the
experimental data and the paramenters underwhich each test was run such as
time, temp, load, ... I know my data table is to large because I'm getting
"Property Value is too large" when I try to save it. Some of the experiments
are related to each other such as Wear tests or Corrosion tests. I want to
split the table but I'm not sure how. I've considered putting each type of
experiment it it's own table or putting the the parameters in a seperate
table. Also some tests are only run on certain types of samples but some are
run on all types of samples. Could someone give me suggestions as to how to
split the data table?

 

[John W. Vinson]

I have set up a database to hold data and test parameters. Currently I have
two main tables, one is a catalog of samples including a catalog
number(primary key), description, location, ... My other table has all the
experimental data and the paramenters underwhich each test was run such as
time, temp, load, ... I know my data table is to large because I'm getting
"Property Value is too large" when I try to save it. Some of the experiments
are related to each other such as Wear tests or Corrosion tests. I want to
split the table but I'm not sure how. I've considered putting each type of
experiment it it's own table or putting the the parameters in a seperate
table. Also some tests are only run on certain types of samples but some are
run on all types of samples. Could someone give me suggestions as to how to
split the data table?

"Property Value is too large" probably has nothing whatsoever to do with
having too much data. At what point do you get the error? in table design
mode, or after you've added data to a table?

You should CERTAINLY have more tables... but splitting it into one table per
experiment is not the way to go. You need a separate table for each kind of
Entity (real-life thing, person or event) of importance, and an additional
table to model each many to many relationship. I don't know the details of
your experiments, but I can visualise a table structure like:

Samples
CatalogNumber <text, primary key>
Description
Location
SampleType
<other info about the sample as an entity>

SampleTypes
SampleType <Text, primary key>

Protocols <specifications for the different kinds of experiments to be run>
ProtocolID <Autonumber, primary key>
ProtocolName <e.g. "Short term high humidity corrosion">
<other info about the protocol itself>

ExperimentParameters <don't use Parameters, it's a reserved word>
<you don't say what parameters are or how they're used but I'd expect you'll
need a table, perhaps another table related to ProtocolID to indicate that a
given parameter is used in a specific protocol>

ResultsHeader
ResultID <autonumber primary key>
CatalogNumber <which sample was tested>
ProtocolID <what experiment was run on it>
TestDate <date/time, when it was run>

ResultsDetails
DetailID <autonuber primary key>
ResultID <long int, link to ResultsHeader>
ParameterID <link to parameters>
Result <the result for this paramter, you may need several result fields if
some parameters give yes/no, others floating point numbers, yet others text
comments>

This can be a pretty complex application: my first exposure to relational
databases involved pharmaceutical screening results on chemical compounds, and
ended up with several hundred protocols with anywhere from one to 300 specific
results depending on the protocol.

 

[bymarce]

"Property Value is too large" probably has nothing whatsoever to do with
having too much data. At what point do you get the error? in table design
mode, or after you've added data to a table?

You should CERTAINLY have more tables... but splitting it into one table per
experiment is not the way to go. You need a separate table for each kind of
Entity (real-life thing, person or event) of importance, and an additional
table to model each many to many relationship. I don't know the details of
your experiments, but I can visualise a table structure like:

Samples
CatalogNumber <text, primary key>
Description
Location
SampleType
<other info about the sample as an entity>

SampleTypes
SampleType <Text, primary key>

Protocols <specifications for the different kinds of experiments to be run>
ProtocolID <Autonumber, primary key>
ProtocolName <e.g. "Short term high humidity corrosion">
<other info about the protocol itself>

ExperimentParameters <don't use Parameters, it's a reserved word>
<you don't say what parameters are or how they're used but I'd expect you'll
need a table, perhaps another table related to ProtocolID to indicate that a
given parameter is used in a specific protocol>

ResultsHeader
ResultID <autonumber primary key>
CatalogNumber <which sample was tested>
ProtocolID <what experiment was run on it>
TestDate <date/time, when it was run>

ResultsDetails
DetailID <autonuber primary key>
ResultID <long int, link to ResultsHeader>
ParameterID <link to parameters>
Result <the result for this paramter, you may need several result fields if
some parameters give yes/no, others floating point numbers, yet others text
comments>

This can be a pretty complex application: my first exposure to relational
databases involved pharmaceutical screening results on chemical compounds, and
ended up with several hundred protocols with anywhere from one to 300 specific
results depending on the protocol.

Thanks for the advice. So if I have the experimental conditions
(parameters, ie time, temp, etc...) in their own table and multible samples
are run on the same experiment under the same conditions they could link to
the same set parameters record? Would I be able to set up a combo box with
sets of parameters my users could choose from and be able to enter their own
even with the parameters in individual fields? Could I set up the data base
to recognize a group of parameters and fill in the appropriate parameter ID
if that set already has a record in the parameter table? Also with the
parameters table, will it be a problem if not all tests have the same
parameters? Would my users have to learn the parameter IDs and Protocol IDs
or could I keep that in the background? I didn't realize how complex this
would be when I started. Thanks again for your help.

 

[bymarce]

Thanks for the advice. So if I have the experimental conditions
(parameters, ie time, temp, etc...) in their own table and multible samples
are run on the same experiment under the same conditions they could link to
the same set parameters record? Would I be able to set up a combo box with
sets of parameters my users could choose from and be able to enter their own
even with the parameters in individual fields? Could I set up the data base
to recognize a group of parameters and fill in the appropriate parameter ID
if that set already has a record in the parameter table? Also with the
parameters table, will it be a problem if not all tests have the same
parameters? Would my users have to learn the parameter IDs and Protocol IDs
or could I keep that in the background? I didn't realize how complex this
would be when I started. Thanks again for your help.

I was trying to start implementing your advice. I'm not sure how to set up
the protocols table. Some tests have one datapoint and some have multiple
data points. We run an oxidation corrosion test on fluid samples with five
sets of 4 metals. Each metal would repersent a data point and usually only 1
set of metals is run for a given sample. How do I handle tests with multiple
data points?

 

[John W. Vinson]

I was trying to start implementing your advice. I'm not sure how to set up
the protocols table. Some tests have one datapoint and some have multiple
data points. We run an oxidation corrosion test on fluid samples with five
sets of 4 metals. Each metal would repersent a data point and usually only 1
set of metals is run for a given sample. How do I handle tests with multiple
data points?

With multiple records in another table, of course: any time you have a one to
many relationship you need two tables, one for the "one" and another for the
"many".

Since I don't know your data model *or* your business model in detail, it's
hard to be really specific about the perfect table structure. The
pharmaceutical test result table structure I described took over a year to
develop, test and implement, and I had a lot of help from some very bright
people to do it. I really hesitate to give you "expert sounding" instructions
which may (in my ignorance of your situation) lead you astray!

I'd suggest studying up on the techniques of normalization in the references
below, or contracting with a professional database developer to help you with
the project - or both. This is probably a bigger app than would be appropriate
for "design by newsgroup"!

 

[John... Visio MVP]

Following up with John's last response, I can help you! I provide help
with Access applications for a very reasonable fee. It looks like what you
need is for someone to review your existing data table and create a new
set of related tables to hold your test data. That would be relatively
easy and not take a lot of time. Then once the tables are created, the
data in your existing data table would need to be mugrated to the new
tables. Again that would be relatively easy and not take a lot of time. I
have done steps one and two many times and can say from experience that my
fees would be very reasonable to do this for you. If you are interested,
email me a copy of your database to (e-mail address removed) and I will gladly give
you a quote of my fees.

Steve

What part of FREE peer to peer support do you not understand? Should we
complain to Roberta and you ISP to get you to go away for another year?

John...

 

[John W. Vinson]

Following up with John's last response, I can help you! I provide help with
Access applications for a very reasonable fee. It looks like what you need
is for someone to review your existing data table and create a new set of
related tables to hold your test data. That would be relatively easy and not
take a lot of time. Then once the tables are created, the data in your
existing data table would need to be mugrated to the new tables. Again that
would be relatively easy and not take a lot of time. I have done steps one
and two many times and can say from experience that my fees would be very
reasonable to do this for you. If you are interested, email me a copy of
your database to (e-mail address removed) and I will gladly give you a quote of my
fees.

Steve, you must have a cast iron skull. Hasn't it gotten through that WHAT YOU
ARE DOING IS UNETHICAL AND WRONG?

These newsgroups are *not* for job solicitation. Anyone familiar with
newsgroup etiquette would know that anyone who advertises as you do is
unethical and not to be trusted with either work or money.

You're doing yourself more harm than good by persisting in posting your
soliciations.

 

[bymarce]

Thanks for the help. I decided to split my large table into three tables all
with a 1 to 1 relationship (data, conditions, and results). I also found
that I whenever one changes the data type of a feild that Access saves it as
a new feild and that I needed to reset the feild count for my table by saving
it under a new name, deleting the original table, and changing the new table
name back to the old name. I know my database isn't completely normalized
but it's much better and good enough. Thanks again.