# Standards for storing a rating as a range (1-5, 1 is best, etc.)

database-designfieldsrange-types

I am designing a ROAD_INSPECTION table that has a CONDITION field:

+---------+-----------+
| ROAD_ID | CONDITION |
+---------+-----------+
|       1 |         2 |
|       2 |         5 |
|       3 |         1 |
|       4 |         1 |
|       5 |         4 |
+---------+-----------+


Are there any standards or conventions that suggest how to format ratings as ranges?

Example, what's the best format for the CONDITION rating?

• 0 to 5, zero is best
• 0 to 5, five is best
• 1 to 5, one is best
• 1 to 5, five is best

Rule of thumb: The higher, the better.

All that said, I'd suggest to use a lookup table to store rating definitions. Maybe for an specific question you want to use a rating of 0 to 5, but may be tomorrow you need a bigger or a lower ratio.

Or simply you want to change the name of each option.

1. Elated
2. Happy
3. Indifferent
4. Unhappy
5. Miserable

Using a lookup table you can choose different options for different purposes. Sometimes you need 5 options, sometimes 10 and sometimes 2.

Do not restrict yourself with a 5 options.

Life has a lot of flavors:

o True
o False
o Do not know/ Do not answer