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
,>5000
and the corresponding values found in each bucket are.80
,.90
,1.0
,.98
. Wind Resistance
<5
,5-10
,>10
and corresponding values.08
.2
.53
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