Having values or some state in a database Suppose I have a table with several price values in it

ID Int, Primary Key, not null
AveragePrice Decimal, not null
MinPrice Decimal, not null
MaxPrice Decimal, not null


Each price value can be known and present (represented as a positive value) or it can be


infinite (without upper bound)
unknown
irrelevant
customer specific


There may be more even states.

How to model this in a database?

Currently these states are modelled as negative constants, e. g. a price of -10 means "unknown", -20 means "irrelevant". Since prices are normally positive this kind of works but I hope there exists a more elegant solution.
There is already a mechanism in SQL to flag missing information. It is NULL. It should be considered a flag indicating meta data about the corresponding column rather than a value of that column itself.

If NULL is too blunt &amp; you need to distinguish various reasons for data being missing you can add your own flags. These will not have built-in support, of course. You will have to implement them as additional columns within the table and check them explicitly in code. You will have to think through the truth tables for your new flags so out handle them consistently in predicates and logic.

If you have to hold customer-specific prices then you will need a table with a customer key in it. Something like:

PriceID Int, Primary Key, not null
CustomerID Int, Primary Key, not null
AveragePrice Decimal, not null
MinPrice Decimal, not null
MaxPrice Decimal, not null