I am somewhat of a noob and perhaps someone out there may have an idea how to help me.
I have a single database with over 3000 tables. Each table is simply a variable read from a production machine (complex, to say the least). Each table has only 2 fields - Read_time and Value. Some tables have only a few records, while others have hundreds (1 record every 10 seconds).
What I am trying to do is to reorg the 3000 tables into 1 with Read_time being a key field. Something like: Read_time | Varname1 |Varname2|Varname3|... timestamp1| Value |Value |Value |... timestamp2| Value .... .... ... timestamp3|....
Any hot tips for me? I have never seen a database organized this way before. Sorry if my similated table doesn't look much like a table. It looked ok when I edited it.
asked Jan 28, 2010 at 03:38 PM in Default
Without a more complete set of requirements, any suggestions made should be taken with a large grain of salt...
From what you've laid out, the basic approach would be to get a table with three fields.
FieldA = The original table name, which I assume reflects a client or app or some other category. I'd make it a field with an fk to another table that actually stores the name FieldB = Read_Time FieldC = Value
I don't see why you'd have multiple Read_Time & Value fields based on what you provided for requirements. So in short two tables, one that is just there as a look for data consistency.
answered Jan 28, 2010 at 03:52 PM
Grant Fritchey ♦♦
Grant's suggestion makes perfect sense to collapse the 3000 tables to a single table if what you're looking it are truly 3k tables w/ two columns, Read_Time and Value, which is how you've described the tables.
But, I'm not sure what this was supposed to be:
Read_time | Varname1 |Varname2|Varname3|... timestamp1| Value |Value |Value |... timestamp2| Value .... .... ... timestamp3|
I'm guessing that we need to explore your requirements a bit further. In the current tables, you mention some have a few rows, others hundreds. In each tables, is that value type always the same, or could the values be for different types of things (within a single table)?
answered Jan 28, 2010 at 11:55 PM