mardi 3 mars 2015

Table Design For Patterned Data Using Variable Bucket Ranges


Looking for table design for this scenario to be implemented on SQL Server 2012 instance.


Scenario


There are a total of seven types of reported data, each with variable number buckets of information.


Each bucket identifies a range such as <5, 5-10, 11-15, or >15. But ranges are specific to each data type and the next type of data will have different bucket ranges; such as <200, 200-210, >210. Hence, making each of the ranges unique to each type of data being collected.


The only commonality is that the values which will fall into each bucket are the same; a decimal value.


Example Data


Trivialized example for car information which each type of data has its own range buckets.



  1. Reported data types: RPM, Wind Resistance and Lateral Accelerations.

  2. RPM has these Range buckets: <1000, 1000-4000, 4000-5000,>5000 and the corresponding values found in each bucket are .80, .90, 1.0, .98.


  3. Wind Resistance



    <5, 5-10, >10 and corresponding values .08 .2 .53





  4. Lateral Accelerations



    <0.2, .2-.4, .4 - .6, .6 - .8 >.8 corresponding values .2, .3, .8, .1, .2





So looking at the data types, RPM has 5 buckets, Wind Resistance only has 3 and Lateral Accelerations has 6 buckets.


Table Schema Attempted


So please review this attempt and suggest an alternative if possible.


X is a placeholder for the table name related to the ultimate data type.



X_Data
PK - X_id int
FK - OperationsId int -- This associates data to a specific process (a report to be printed out)
Bucket1 decimal
Bucket2 decimal
...
BucketN decimal -- N would be the number which is only needed for `X` data.


This would be repeated for a total of seven tables.


Summary


If the above table design is not optimal, what design would be best to handle at 7 minimum and 10 maximum data reporting needs. Each data need has 3-10 individual buckets of differing sizes and each of the final data in a *bucket` is of type decimal.


There is an overall pattern, but within the pattern there exists variability to be addressed...hence I wonder if any one else designed for such a scenario.





Aucun commentaire:

Enregistrer un commentaire