Mapping arbitrary number of attributes to DB

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?

Thanks!

j

--

Joshua Kugler
Lead System Admin -- Senior Programmer
http://www.eeinternet.com
PGP Key: http://pgp.mit.edu/  ID 0xDB26D7CE

--

Posted via a free Usenet account from http://www.teranews.com Received on Sat Oct 21 2006 - 03:15:08 CEST