Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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:

  1. keep the threshold field (only) on the sample, and trigger this to get filled in when the project is saved.

  2. 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?

share|improve this question
    
Assuming I understood your question correctly I'd have a threshhold field at the sample and provide a default value (the project threshold) for that field. You will need to provide a means to provide that value on the sample granularity anyway, and with a default value you avoid additional logic at runtime. –  Thomas Apr 19 '13 at 15:33
    
I think this would be easier to answer if you gave a clear example. –  FrustratedWithFormsDesigner Apr 19 '13 at 16:11

3 Answers 3

up vote 1 down vote accepted

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.

share|improve this answer

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 ...
share|improve this answer

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.

share|improve this answer
    
I hadn't really though of that problem, but in our case the project level threshold should not be getting updated. –  wobbily_col Apr 21 '13 at 16:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.