Re: Need database design advice

From: Michael C (michaelco_at_optonline.net)
Date: 06/07/04 

Date: Mon, 07 Jun 2004 04:10:48 GMT

Usually you'll set up separate tables that will look something like this:

TABLE: Instruments
FIELDS: InstrumentID, InstrumentName 

---
TABLE: TemperatureMeasurements
FIELDS:  UniqueID, InstrumentID, DateMeasured, Temperature
---
TABLE: WaterMeasurements
FIELDS:  UniqueID, InstrumentID, DateMeasured, WaterPercent
This eliminates all the blank rows you were talking about, as only the data
you actually want to store is stored.  It also eliminates having to keep a
separate table for each instrument, which makes adding, removing or
replacing instruments in the future much easier (all you have to do is
change a row in the Instruments table instead of changing the structure of
the database).  Finally, each measurement type is stored with the ID of the
instrument that was used to measure it, so you can reference any or all
measurements taken by a specific instrument.  Depending on what type of
measurements you're taking you might even be able to make a single
Measurements table with a field specifying which type of measurement,
instead of having separate tables for each type.  That way if you get a new
set of measurement criteria (i.e., weight or altitude, or whatever) in the
future, it will be much easier to update.  It depends on the other
information you're trying to store though.
You would access information with JOIN queries, like this:
SELECT * FROM Instruments i INNER JOIN TemperatureMeasurements t ON
i.InstrumentID = t.InstrumentID AND i.InstrumentID = 1092
This SELECT would pull up all Temp measurements for Instrument #1092.  You
can add other criteria, such as date ranges, etc., to this query.  Lots of
big books have been written on database design, and there's a lot of info
out there.  Look up NORMALIZATION on google for more specifics.
Thanks,
Michael C.
"Mr m?ll" <salviakrydda@yahoo.se> wrote in message
news:e19c73c1.0406061536.27c3ad7@posting.google.com...
> i 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

Relevant Pages

  • Re: Python reliability
    ... measurement instruments thrgough the network. ... then an alarm is set. ... A false alarm is costly, ...
    (comp.lang.python)
  • Re: Resolving the fine line spectras of LCD spectra
    ... that the measurement by high and low resolution ... for the conversion of the spectra into XYZ values. ... narrow large peaks are ... Better instruments - more accurate results. ...
    (sci.engr.color)
  • Need database design advice
    ... keep track of several measurement instruments. ... I'll have alot of empty columns. ... SQL-query of those strings. ...
    (microsoft.public.sqlserver)
  • Need database design advice
    ... keep track of several measurement instruments. ... I'll have alot of empty columns. ... SQL-query of those strings. ...
    (microsoft.public.access.tablesdbdesign)
  • Summer Music School Pucisca 2006, Island Brac, Croatia
    ... It covers different musical genres in separate ... music workshops divided by instruments. ... Each instrumental course is held in a separate 10-day session and consists ... Zastupljene su sve vrste glazbe ...
    (rec.music.theory)
Loading