I have a database that contains projects and each project contains many samples. I want a project level threshold value, to trigger highlighting when a field on the sample is above this threshold. I also want to be able to override the threshold at the individual sample level.
So I can see two ways to implement this:
-
keep the threshold field (only) on the sample, and trigger this to get filled in when the project is saved.
-
have both sample and project level threshold fields, and only use the sample field when it is relevant (there seems to be some redundancy with this model, though it more accurately represents the situation)?
Are there advantages / problems with either approach?
2
Its actually a simple descision based on how often you update the project threshold vs. how often you query the samples.
If the project level threshold is not updated very often then I would copy the threshold to every sample row (with perhaps an extra column to indicate an overridden value). This will make any queries simpler and faster.
On the other hand if you update the default threshold frequently. The keep it at the project level and take the (slight) performance hit for the join for every query.
Your 2nd option provides the most flexibility with minimal extra complexity.
In the sample record, have a nullable field that holds the local threshold level. Set the field to null if the sample is to use the default threshold from the project. If you are using SQL, it is straight-forward to join the tables and use isnull to grab the threshold value:
SELECT Field1, Field2, ISNULL(sample.threshold, project.threshold) as Threshold FROM ...
I would go with your second option, only store the override value on the sample records when it is different to the project threshold (stored on the project record).
These seems like the cleaner solution to me (storing info about the project with the project record, and the sample specific data with the sample) but more importantly there’s a problem with your first option.
For example, if you store the project threshold on the samples where there is no sample specific threshold and you have a project threshold of 5:
| Sample Name | Threshold |
| Sample A | 5 |
| Sample B | 5 |
| Sample C | 5 |
You update Sample C to have an over-ridden threshold of 6:
| Sample Name | Threshold |
| Sample A | 5 |
| Sample B | 5 |
| Sample C | 6 |
You then update the project threshold to 6:
| Sample Name | Threshold |
| Sample A | 6 |
| Sample B | 6 |
| Sample C | 6 |
If you should go on to change the project threshold back down, you can’t distinguish C from A and B, meaning it will also be changed back to 5, which might not be what you want.
You could store a flag to indicate that the threshold is sample specific to avoid this situation, but this seems redundant when compared to storing the project threshold on the project and having nulls in the sample threshold unless it is set.
1