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.
- Reported data types: RPM, Wind Resistance and Lateral Accelerations.
- RPM has these Range buckets:
<1000,1000-4000,4000-5000,>5000and the corresponding values found in each bucket are.80,.90,1.0,.98. Wind Resistance
<5,5-10,>10and corresponding values.08.2.53Lateral Accelerations
<0.2,.2-.4,.4 - .6,.6 - .8>.8corresponding 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