Database for Scientific Data

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?

On Tue, 17 Jun 2008 13:26:01 -0700, bymarce
wrote:

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.
--

John W. Vinson [MVP]

"John W. Vinson" wrote:

On Tue, 17 Jun 2008 13:26:01 -0700, bymarce
wrote:

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.
--

John W. Vinson [MVP]


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" wrote:


"John W. Vinson" wrote:

On Tue, 17 Jun 2008 13:26:01 -0700, bymarce
wrote:

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.
--

John W. Vinson [MVP]


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?

On Fri, 20 Jun 2008 08:34:03 -0700, bymarce
wrote:

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 W. Vinson [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 and I will gladly give you a quote of my
fees.

Steve



"bymarce" wrote in message
...
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?

"Mark" wrote in message
m...
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 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...

"Mark" schreef in bericht m...
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 and I will gladly give you a quote of my
fees.

Steve


--
Hey Mark/Steve!! We don't need you here !!
Hey Mark/Steve!! We don't want you here !!

This is to inform 'newbees' here about PCD' Steve:
http://home.tiscali.nl/arracom/whoissteve.html (updated, mainly the 'abuse-reporting' page...)
Until now 5750+ pageloads, 3600+ first-time visitors
(these figures are real and rapidly increasing)

Why is this ???
Because Steve is the ONLY person here who continues to advertise in the groups.

If anyone wants to help us getting rid of Steve ??
(appropriate action will follow when there are enough complaints)
*********************************

Arno R

On Tue, 24 Jun 2008 11:26:40 -0400, "Mark" wrote:

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 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.
--

John W. Vinson [MVP]

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.

"John W. Vinson" wrote:

On Fri, 20 Jun 2008 08:34:03 -0700, bymarce
wrote:

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 W. Vinson [MVP]