Re: Looking for table design input
Date: Mon, 30 Mar 2009 06:57:36 +0000 (GMT)
I am currently proposing such a design to one of my projects
ID - Code1 - Code2 - Code3 - Count1 - Count2 - ... Count15
Reason this is a part of a online application where the data needs to be shown on a webfront end. This means the response time needs to be typically less then 10 seconds.I tested the approach that you had earlier and in my case with 42 Million rows and all kind of sql pivoting techniques applied i cannot get the answer in less then 10 seconds unless i design my data structure in the way you are suggesting.
Best way to do this i can think of is the hard way only.To answer the query upfront and design it that way.
Downside to the approach is
Some sort fo ETL needs to be designed so the data is structured that way. (i was thinking of Oracle streams replication to do this but ran out of ideas on how to implement that) Tomorrow if more counts are added you need to add more columns (in my case i would have to add more columns) earlier approach does not require manual intervention and would be just inserting more rows.