Re: Mapping arbitrary number of attributes to DB

Joshua J. Kugler wrote:
> Hello all.
>
> A little background: I've done a fair bit of database application
> programming over the years, starting with MS Access 2 and 97 <shudder> and
> have read a lot about database theory, starting with this book:
> http://www.amazon.com/SQL-Structured-Language-Carolyn-Hursch/dp/0830638032/sr=1-4/qid=1161392334/ref=sr_1_4/104-7369811-8083948?ie=UTF8&s=books
>
> I'm very comfortable with 1NF (and maybe even 2NF, 3NF and higher, until my
> head starts to hurt), so I can generally design tables and relations I
> need.
>
> I've got a problem now that, while I think I have figured out how to do it,
> I'm wondering how others have solved this problem. I've tried googling,
> but I really don't know the words to use to search.
>
> 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
> on the number of sensors attached to the particular unit sending the data.
>
> Creating a table for each datafile does not seem practical (proliferation of
> tables, plus the number of columns can change, making ongoing maintenance a
> nightmare).
>
> So here is what I'm thinking about doing.
>
> Table: Station
> id
>
> Table: Station_log (stores information about each line in the datafile)
> id
> station_id
>
> Table: Stored_data
> id
> Station_log_id
> column_num
> data_value
>
> Where column_num would be the column number in the original file, and
> data_value (a float) would be the value in that column. So, for each row
> in a data file, there will be one row in Station_log, and for each *column*
> in a row, there will be one row in Stored_data.
>
> I think this is the best way to do things, keeping everything relationally
> sound, and getting at a series of values later for a particular column
> should be rather simple.
>
> Other ideas on how to accomplish storing this kind of data?
> Resources/reading to which you can point me? Other tips?
>

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.

-- 
David Portas