Re: Mapping arbitrary number of attributes to DB

From: Bob Stearns <rstearns1241_at_charter.net>
Date: Tue, 24 Oct 2006 00:56:28 -0400
Message-ID: <12h%g.1505$Dg6.202@newsfe07.lga>

Joshua J. Kugler wrote:
> Cimode wrote:
>

>>>Here it is: the application is going to be importing data from files
>>>that contain sensor readings.  These files can have an arbitrary number
>>>of columns, anywhere from maybe three or four all the way up to 200+
>>>depending
>>
>>No need to go any further to tell you this is the wrong direction 

>
>
> And David Portas wrote:
>
>

>>I would have expected the file to be no more than a convenient medium
>>for interchanging the data. In that case the file structure is surely
>>the least relevant part of the problem - yet you have apparently based
>>your whole model around it!
>>
>>The route to an effective logical design is via an understanding of the
>>concepts you are trying to model. I don't think columns in files will
>>help achieve that. Even given the limited information you supplied your
>>design still looks highly impractical.

>
>
>
> Sadly enough, I agree with both of you. The problem is, part of the way the
> data is represented is out of my control. Data is coming in from weather
> stations. Some of these stations may have 1 sensor (e.g. temperature),
> some of these stations may have many sensors (temperature, wind speed, soil
> temperature, humidity, air pressure, many of them redundant). Thus, each
> station can have a number of attributes that vary from one to dozens. I
> can't tell the scientists "You may have 32 sensors per station, no more, no
> less." That just won't work, for reasons I hope are obvious. :)
>
> What I'm trying to do is find a generic way to map these attributes to a DB
> so that I can import and process any data file. I know the kind of design
> I proposed ("flattening" it to one value per row) isn't the best, but right
> now, I'm not sure how else I'd go about it. That's why I asked for ideas.
>
> So, I guess stated another way, forget about those files...how might I go
> about (or where might I read about) mapping an arbitrary number of
> attributes to a database in a clean, efficient way?
>
> Thanks!
>
> j
>

What is wrong with a model like:

Station(stationid, location, name, etc)
Instrument(instrumentid, datatype, rangemin, rangemax, etc) StationInstruments(stationid, instrumentid, positionininputstream) Recordings(stationid, instrumentid, recordingid [maybe a timestamp],

        value)

When new instrument types come on line new Instrument rows are added. If   instruments deliver non numeric data, then the simple (rangemin, rangemax) will have to be extended in fairly obvious ways. Received on Mon Oct 23 2006 - 23:56:28 CDT