Click here to Skip to main content
15,867,986 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I need to store Saunderson correction factors in a database and I am unsure about the correct table design.

Saunderson correction factors are used in a calculation model that calculates the optimal mix of mixing colors for creating a color (measured using a spectrophotometer).
The factors consist of two values called K1 and K2 and they recorded:
  • for 31 wavelengths
  • <d>from 400 until 700 in increments of 10
  • for one or more angles
  • <d>-15, 15, 25, 45, 75 and 105 degrees
  • for multiple paint qualities
  • <d>For a particular quality and angle 31 K1 factors and 31 K2 factors are stored.
    These 62 values are linked and have no individual meaning. Leave out one value and the calculation would fail.
    There is no database filtering based on these factors.

Changes in the number of factors:
K1 and K2 are a fixed pair of values and they are an integral part of the calculation model, so the pair will not be expanded with more values.
Although highly unlikely the number of wavelength would change.
The angles are fixed in the hardware of the spectrophotometer; so this makes it also highly unlikely that the angles will change.
Database retrieval:
The factors are retrieved as a set of 62 values.
The factors are constant for the lifetime of the application/module; they will be retrieved irregularly; so retrieval should not cause any performance issues.
Database storage:
A few times a year the complete set of 62 values is replaced; so again database access should not cause any performance issues.
Possible designs:
Underline is the primary key
  1. Quality, Angel, Wavelength, K1, K2
  2. Quality, Angel, Wavelength, K, Value
  3. <d>K=1 is for the K1 factor and K=2 is for the K2 factor.
  4. Quality, Angel, Wavelength, Value
  5. <d>Using this design I would need to create two tables one with the name '..._K1' and the other '..._K2'
    This one does not feel right; like having two the equal tables called student and teacher


What is the properly normalized design for this table?

To limit the angle and wavelength fields to the correct values should I use a check constraint or lookup tables?
Posted
Updated 3-Feb-14 7:29am
v2
Comments
Matt T Heffron 3-Feb-14 13:54pm    
I'd use #1.
The K1 and K2 are actually a single "thing" with two "components".
Jörgen Andersson 6-Feb-14 16:28pm    
# 1 with lookup tables.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900