Modeling data with levels of detail, some of which are missing
I am making a data model for derby videos to track their matches. I keep track of things like lap times, lap penalties, period penalties, and match penalties.
The problem is that in some cases I will only have general data; I may have "match penalties" for one match and "period penalties" for another. Therefore, at the lowest level for some matches, I will have very detailed data (penalties per hour), and at the highest level, I will have penalties per match.
I'm not sure how to model / use this to generate reports when I don't have fine granularity for some of the records. I thought of something like this:
PenaltiesPerMatch MatchID PenaltyCount
PenaltiesPerPeriod MatchID PeriodID PenaltyCount
PenaltiesPerLap MatchID PeriodID Lapid PenaltyCount
But I am concerned that higher level information can be obtained from a lower level. Should I duplicate records (for example, fill out a record of penalties for a period with data that also refer to penalties for one lap, summed up by period?) Or store unique records (do not enter penalties for a period for data that I already have in the form of penalties on your knees, calculate it by summing over the period).
a source to share
What I would do is write down the information that you have. For some matches, write it down in detail, for others, in detail.
When you report matches:
- Calculate the sums for each match for detailed matches
- Use the amount per match from the smallest detailed matches
Save data at the lowest granularity you have; calculate higher levels of detail.
a source to share
You can store information in a single table, with NULL values indicating that you have no data up to that level. You couldn't transfer the primary key to it, so you need a surrogate key, but you can use a unique constraint.
For instance:
CREATE TABLE PenaltyCounts
(
penalty_count_id INT NOT NULL,
match_id INT NOT NULL,
period TINYINT NULL CHECK (period BETWEEN 1 AND 3),
lap SMALLINT NULL,
penalty_count SMALLINT NOT NULL,
CONSTRAINT PK_PenaltyCounts PRIMARY KEY NONCLUSTERED (penalty_count_id),
CONSTRAINT UI_PenaltyCounts UNIQUE CLUSTERED (match_id, period, lap),
CONSTRAINT CK_lap_needs_period CHECK (lap IS NULL OR period IS NOT NULL)
)
One problem with this, for which I don't see an easy solution yet, is how to ensure that they can ONLY impose penalties at the same level. For example, they can still do this:
INSERT INTO PenaltyCounts (penalty_count_id, match_id, period, lap, penalty_count)
VALUES (1, 1, NULL, NULL, 5)
INSERT INTO PenaltyCounts (penalty_count_id, match_id, period, lap, penalty_count)
VALUES (2, 1, 1, NULL, 3)
INSERT INTO PenaltyCounts (penalty_count_id, match_id, period, lap, penalty_count)
VALUES (3, 1, 2, NULL, 2)
The advantage of this one table solution is that all of your statistics can be found by querying one table and GROUP BYs will hide everything perfectly.
You can also use a separate table method, but overlay views on them to bring everything together. This still allows for a higher problem than placing numbers on multiple levels.
a source to share
I think it depends on what information is valuable to the client. If they would like information on a period, then you should include this as a separate entry. The penalty for the period and the match must be separated.
You always had information about the penalty for the period, then you could run a query that summarizes the data.
If your periods are always a fixed number, then you can probably just make two columns in the table instead of a new table to hold the period information
a source to share