Need database design advice

[Mr m?ll]

Hi everyone!
I am quite new to designing databases and have a problem. It is a bit
tricky to explain...I would really appriciate help from anyone
experienced
here

I'm makeing a program using MS SQL Server (actually MSDE) and want to
keep track of several measurement instruments. The instruments can
measure data of different data types, which are reported to the DB-prgram:

Instrument1: temp(int), wind vel.(int)
Instrument2: water%(float), userinput(varchar(20)), camera
snapshot(Image)
....and so on


Sometimes the measurements from the different instruments should be
considered
as one measurement, like for example you should be able to find the
measurement,M,
where the temp was "55" and the water% was "87.7".

Now to the question:
Should I use one big table holding every value that can be measured by
any instrument.This would solve alot of problems, but most of the time
I'll have alot of empty columns.

|----------------------
|AllValues |
-----------------------
|measurementID |
|value1forInstrument1 |
|value2forInstrument1 |
|value1forInstrument2 |
|value2forInstrument2 |
|value3forInstrument3 |
| ... |
-----------------------

....or should I have one separate table for every instrument?
This sounds nicer but in that case I get some problems:


I have to name the tables names like "Instr1Table", "Instr2Table". Say
that I then want to get all values from measurement M. To be able to
go through all tables I have to have a list of all my tables. The only
solution I can come up with is have a table with the strings
"Instr1Table", "Instr2Table" and loop through that list and make a
SQL-query of those strings.

PS
There are 2-5 instruments, wich measures 5-10 different values
The number of instrument will probably rise in the future. So this
means I either have to extend my AllValues-table or add another
"InstrXTable"-table
DS


Very greatful for answers, thanks in advance, Möll

 

[John Vinson]

I have to name the tables names like "Instr1Table", "Instr2Table". Say
that I then want to get all values from measurement M. To be able to
go through all tables I have to have a list of all my tables. The only
solution I can come up with is have a table with the strings
"Instr1Table", "Instr2Table" and loop through that list and make a
SQL-query of those strings.

PS
There are 2-5 instruments, wich measures 5-10 different values
The number of instrument will probably rise in the future. So this
means I either have to extend my AllValues-table or add another
"InstrXTable"-table

This sounds like a good case for the technique of "Subclassing" - one
of the few instances where one to one relationships make sense.

Consider having a Measurement master table, related one to one to
Instr1Table, Instr2Table and so on. You could create a Query with Left
Outer Joins to each specific table, joining on the primary key; this
would give you values for those instruments for which there is data,
and NULL for the ones that don't.

 

[Mr m?ll]

This sounds like a good case for the technique of "Subclassing" - one
of the few instances where one to one relationships make sense.

Consider having a Measurement master table, related one to one to
Instr1Table, Instr2Table and so on. You could create a Query with Left
Outer Joins to each specific table, joining on the primary key; this
would give you values for those instruments for which there is data,
and NULL for the ones that don't.

Thanks for your answer
I also posted this message at
http://groups.google.se/[email protected]&rnum=2

and got a similar answer, but using a table for every value. In the
question
I didn't tell all the details, but in my specific application that one
seemed
better.

Anyway, thanks alot , Möll