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

Best Answer

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