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

Question

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

---

Answer 1

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

Answer 2

I'm not aware of any official standard for this sort of thing. I would be inclined to base whether 5 is worst or best based on what will be easiest to work with in your case.

A scale of 1-5 is the most common. If you want a separate value to indicate no answer (or that the responder indicated the question did not apply to them), I would use NULL over 0; that makes it easy to ignore those responses in calculations (as aggregate functions do just that).

Also, keep in mind anything commonly in use in the field this will be applied to. For example, hospitals and doctor's offices in the US normally ask patients to rate their pain on a 0-10 scale (0 = no pain, 10 = the worst pain imaginable).

Answer 3

Is the number the thing that end users will see in reports and on-screen or is it a place-holder for another value?

As a place-holder (ie a foreign key into a CONDITION table) the value used is immaterial. When the users see, for example, "Poor", "Fair", "OK", "Good" and "Excellent" then the integer used behind the scene can be anything. It is not worth spending much time deciding.

If the end-users will see the number then follow common usage. Does the industry have a standard? Is there a common usage within the company? Follow that.

Most people outside computing do not label items or start counting with zero. Avoid that. In English "priority 1" or "1st class" means that item is at the head of the queue. Follow that convention when determining the ranking.

As a note, the ranges 0-5 and 1-5 have a different number of buckets. Ensure your choice maps to the users' granularity.

Answer 4

Some of the other answers suggest that the actual values used are not that important, as long as they are mapped properly to their "meaning" when shown in forms or queries or reports.

I'm going to strongly suggest that is not true, or at least won't be true for long.

Why? Because I guarantee someone is going to ask you for a report that shows an "average" condition rating:

• Is our average road condition (over all roads) better today than this day last year?
• Which roads have the worst and best average rating along their entire length?
• Which areas of town have the worst and best average condition rating?
• Are there inspectors that consistently rate the same roads worse or better than other inspectors?

and a hundred other questions that management loves to be able to ask. Unless your ratings are numeric (and held in either strict ascending or strict descending order), you won't be able to perform these calculations.

That said, I'm not aware of any industry standard for doing so, you just have to be as consistent as you can in your methodology.

For example, it is confusing to take a survey that constantly flip whether 5 is "good" vs "bad":

• How satisfied were you with your meal? (1=very satisfied, 5=not at all satisfied)
• Was your server friendly? (1=Not very friendly, 5=very friendly)

So pick one and stick with it.

Answer 5

I suggest you have a look at the section on 'rating scales' in the complete guide to writing questionnaires:

• match how people think about it. If it is 'bad', 'soso', 'good' use that
• is the rating about left inclination or right inclination? Use some form of angle.
• is it about type of road? Use OpenStreetmap categories
• are values in between allowed? Use numbers, else don't use numbers.
• is it bipolar? Use -2, ..., 2
• make the scale length reasonable. The shorter the better.

How are you going to use the rating? Do you need to calculate aggregates like mean, min or max? Then use numbers. If this is not meaningful, don't use numbers.

What kind of where clauses do you expect? How will the code look like and how does your design contribute to avoiding errors and do your design choices support that the intention of the code is easily and readily conveyed?

Last but not least: Design for clarity and less errors, optimize later.

Hope this helps

Answer 6

I would focus on what you are trying to measure. The rank should be intuitive.

IMO the more of something you measure the number should go up. So if you are measuring how GOOD the road is, I would go 0-5: higher is better.

If you are measuring how BAD or Damaged the road is I would go 0-5: higher is worse.

Personally I believe NULLS should be used to reflect unknown or no answer. If you default it to a value you risk skewing reports, and make it harder to find missing assessments.

However: if you are replacing or upgrading an existing system I would stick with whatever system they have now, unless the users specifically want something different. Otherwise you will have to convert data as you migrate, and also manage training issues when you implement this system. "If it aint broken don't fix it".