Re: Mapping arbitrary number of attributes to DB

From: paul c <toledobythesea_at_dbms.yuc>
Date: Tue, 24 Oct 2006 01:15:37 GMT
Message-ID: <ZOd%g.183480$5R2.114490_at_pd7urf3no>

Joshua J. Kugler wrote:
> David Portas wrote:

>>> 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. :)
>> No-one except you suggested that.

> Sorry, I didn't mean to suggest that anyone did...just thinking out-loud I
> guess.
>> So you'll need attributes for Wind Speed, Temperature, Humidity etc.
>> Obviously those are very different domains so they belong in separate
>> columns. If each type of reading is totally independent of the others
>> then one would expect to see a separate table for each, with a common
>> key presumably comprising the station number and a timestamp. This is
>> of course totally unsupported guesswork on my part.

> What you suggest is good, and might work if I had a fixed domain and set of
> sensors. But the set of sensors, and types, could change at any time. If
> it were feasible to take that approach (currently, it's not), we could go
> into production, and two months down the road, we would need to import data
> for sensor type Zerple, that does not fit in any of our previous profiles,
> and we might have 20 different readings of type Zerple on each sample (line
> of the data file). Creating a new table for each type of new sensor is not
> going to work.
> I admit, I'm working with a problem that is *very* database unfriendly. I
> guess I'll see what I can read and come up with.
> Thanks for your input so far.
> j

There are others here who design a lot more db's than I ever did, but let me make two comments: 1) It seems important to me to ask the 'scientists' how they plan to use/manipulate this data. Eg., you might find out that they plan to summarize it initially and do most of their analysis from summaries, only keeping the raw data for adhoc historical uses, but of course I'm just guessing. Or, if they have their hearts set on using PERL or somesuch or using half an hour of cpu time to crunch data, I might wonder what good is a dbms. Or maybe they aren't sure what they're going to do with it and you could just go upstairs and tell the big kahuna that all they need is an archive system. 2) Regarding the Zerple, I doubt if any dbms could be made that would be 'eternel'. Things change, same with all systems. That's life.

paul c. Received on Tue Oct 24 2006 - 03:15:37 CEST

Original text of this message