By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,475 Members | 954 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,475 IT Pros & Developers. It's quick & easy.

Scientific database design question

P: n/a
I'm designing a database which will store test data from a scientific
instrument. There are approximately 300 variables which can be
collected from this device, although not all tests will conatin valid
data for all variables. Because of this, would it be best to create a
table for each variable? The layout would use the test filename and
current test point as the primary key and a value field to store the
variables status.

Example:
Table - Var_1
Filename(text) - PK
TestPoint(number) - PK
Value(number)
Are there any disadvantages in creating so many tables? Any advice
would be greatly appreciated.

Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Tony,
Dealing with 300 tables in a query would turn into an anxiety making
nightmare. I'd find 300 columns and any number of rows, one per file name,
a lot easier to deal with. In my way of looking at it, there are 300
measured facts and at least three dimensions--file name, device and time.

--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS
"tony" <ca*******@hotmail.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...
I'm designing a database which will store test data from a scientific
instrument. There are approximately 300 variables which can be
collected from this device, although not all tests will conatin valid
data for all variables. Because of this, would it be best to create a
table for each variable? The layout would use the test filename and
current test point as the primary key and a value field to store the
variables status.

Example:
Table - Var_1
Filename(text) - PK
TestPoint(number) - PK
Value(number)
Are there any disadvantages in creating so many tables? Any advice
would be greatly appreciated.

Nov 13 '05 #2

P: n/a
Br
In news:11*********************@g14g2000cwa.googlegro ups.com,
tony <ca*******@hotmail.com> said:
I'm designing a database which will store test data from a scientific
instrument. There are approximately 300 variables which can be
collected from this device, although not all tests will conatin valid
data for all variables. Because of this, would it be best to create a
table for each variable?


Nope :) Keep it dynamic if possible.

Perhaps.....

Test
------
TestID
TestDesc
TestDate

Variables
-------------
VariableID
VariableDesc

TestVariables
--------------------
TestID
VariableID
VariableValue
Each test can then have as many variables as you like.

Or something like that :)

<>

Br@dley
Nov 13 '05 #3

P: n/a
Hi Alan,
Thanks for the response. I was thinking of that layout, but isn't there
a limit on the number of columns in an Access database?
Alan Webb wrote:
Tony,
Dealing with 300 tables in a query would turn into an anxiety making
nightmare. I'd find 300 columns and any number of rows, one per file name, a lot easier to deal with. In my way of looking at it, there are 300 measured facts and at least three dimensions--file name, device and time.
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS
"tony" <ca*******@hotmail.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...
I'm designing a database which will store test data from a scientific instrument. There are approximately 300 variables which can be
collected from this device, although not all tests will conatin valid data for all variables. Because of this, would it be best to create a table for each variable? The layout would use the test filename and
current test point as the primary key and a value field to store the variables status.

Example:
Table - Var_1
Filename(text) - PK
TestPoint(number) - PK
Value(number)
Are there any disadvantages in creating so many tables? Any advice
would be greatly appreciated.


Nov 13 '05 #4

P: n/a
"Br@dley" <n0****@4u.com> wrote in news:_OY9e.19343$5F3.4612@news-
server.bigpond.net.au:
In news:11*********************@g14g2000cwa.googlegro ups.com,
tony <ca*******@hotmail.com> said:
I'm designing a database which will store test data from a scientific
instrument. There are approximately 300 variables which can be
collected from this device, although not all tests will conatin valid
data for all variables. Because of this, would it be best to create a
table for each variable?


Nope :) Keep it dynamic if possible.

Perhaps.....

Test
------
TestID
TestDesc
TestDate

Variables
-------------
VariableID
VariableDesc

TestVariables
--------------------
TestID
VariableID
VariableValue
Each test can then have as many variables as you like.

Or something like that :)

<>

Br@dley


As they say in Family Feud, "Good Answer!". It may be necessary to expand
on your plan by breaking down your Test Variables to Integer Test
Variables, Float Test Variables, String Test Variables or whatever scheme
matches tha data.

--
Lyle

"The aim of those who try to control thought is always the same. They find
one single explanation of the world, one system of thought and action that
will (they believe) cover everything; and then they try to impose that on
all thinking people."
- Gilbert Highet
Nov 13 '05 #5

P: n/a
Tony,
Maybe, but you can punt and split it into two 150 column tables in a
one-to-one relationship. The only wrinkle would be if your report needs all
300 and Access balks because it's too many columns. I suggested 300 columns
because I was hoping you wouldn't need to report all 300 in one view. But .
.. . if you do you may want to look at MSDE, a smaller version of SQL Server
that comes with versions of Office beginning in version 2000.
Then again . . . a UNION of 300 SQL statements pulling from your tables
would gather the whole mess into one result set that could be viewed fairly
easily. It's a big enough number of SQL Statements to pull together that I
still have a gut reaction that there has to be a way to gather it all into
one table which gets new rows each time you collect data. I worry that this
is a big enough task as a dynamic query that performance would be
unnacceptable unless the data is stored in a table as a finished result.
There is also the usual reason for doing a data-mart or star schema in that
it captures a set of snapshots of the data at specific points in time so you
can glean trends in the data.
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS
"tony" <ca*******@hotmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
Hi Alan,
Thanks for the response. I was thinking of that layout, but isn't there
a limit on the number of columns in an Access database?
Alan Webb wrote:
Tony,
Dealing with 300 tables in a query would turn into an anxiety making
nightmare. I'd find 300 columns and any number of rows, one per file

name,
a lot easier to deal with. In my way of looking at it, there are 300

measured facts and at least three dimensions--file name, device and

time.

--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS
"tony" <ca*******@hotmail.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...
> I'm designing a database which will store test data from a scientific > instrument. There are approximately 300 variables which can be
> collected from this device, although not all tests will conatin valid > data for all variables. Because of this, would it be best to create a > table for each variable? The layout would use the test filename and
> current test point as the primary key and a value field to store the > variables status.
>
> Example:
> Table - Var_1
> Filename(text) - PK
> TestPoint(number) - PK
> Value(number)
> Are there any disadvantages in creating so many tables? Any advice
> would be greatly appreciated.
>

Nov 13 '05 #6

P: n/a
tony wrote:
I'm designing a database which will store test data from a scientific
instrument. There are approximately 300 variables which can be
collected from this device, although not all tests will conatin valid
data for all variables. Because of this, would it be best to create a
table for each variable? The layout would use the test filename and
current test point as the primary key and a value field to store the
variables status.

Example:
Table - Var_1
Filename(text) - PK
TestPoint(number) - PK
Value(number)
Are there any disadvantages in creating so many tables? Any advice
would be greatly appreciated.

You could do something like this:
Test Table
----------
FileName
Date/Time
Who did the test
Status
and anyother fields that are always entered for a given test

Test Variables Table
--------------
FileName
VariableName
TestPoint
Value

The Test Table is a one to many to the Test Variables using the FileName
as the primary key , no duplicates and FileName as the primary key for
the Test Variables, duplicates ok. or for the test variable table,
primary key
FileName/VariableName, FileName duplicates ok, VariableName no duplicates

The advantage/disadavantages come down to "How do you want to see the data".
With many tables you are going to need many SubForms on a main Form to show
all of the variables for a given test. Also your queries will be more
complicated.

Ron

--
Ronald W. Roberts
Roberts Communication
rw*@robcom.com

Nov 13 '05 #7

P: n/a
Hi Ronald,
I think yours and the previous posters suggestions are a good solution,
however in the "Test Variables" table I would include "Test Point" in
the primary key, since both file name and variable name will have
repeating values, but the test point will be unique.

Ronald W. Roberts wrote:
tony wrote:
I'm designing a database which will store test data from a scientificinstrument. There are approximately 300 variables which can be
collected from this device, although not all tests will conatin validdata for all variables. Because of this, would it be best to create atable for each variable? The layout would use the test filename and
current test point as the primary key and a value field to store the
variables status.

Example:
Table - Var_1
Filename(text) - PK
TestPoint(number) - PK
Value(number)
Are there any disadvantages in creating so many tables? Any advice
would be greatly appreciated.
You could do something like this:
Test Table
----------
FileName
Date/Time
Who did the test
Status
and anyother fields that are always entered for a given test

Test Variables Table
--------------
FileName
VariableName
TestPoint
Value

The Test Table is a one to many to the Test Variables using the

FileName as the primary key , no duplicates and FileName as the primary key for the Test Variables, duplicates ok. or for the test variable table,
primary key
FileName/VariableName, FileName duplicates ok, VariableName no duplicates
The advantage/disadavantages come down to "How do you want to see the data". With many tables you are going to need many SubForms on a main Form to show all of the variables for a given test. Also your queries will be more complicated.

Ron

--
Ronald W. Roberts
Roberts Communication
rw*@robcom.com


Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.