What field type?

Question

I know maybe this is a stupid question, But i suck at sql

But what field type should you have on a field that can only have numbers 1 to 5? 5 is max and 1 is min. It's currently int(11) not unsigned

I heard having the wrong type can hurt performace

Edit: the database is InnoDB. RDBMS is MySQL

Answer 1

What database platform?

I would generally choose the smallest and add a check constraint for good measure for data quality/integrity.

For SQL Server, that would be tinyint (0-255).

Answer 2

Depending on your RDBMS, go with an integer that can hold a small value.

MySQL has a tinyint datatype, which is of size 1 byte, and holds values 0-255 or -128 through 127.

MySQL apparently doesn't have check constraints implemented in the current version, so you'll either have to enforce your 1-5 range requirement yourself in other code. There are some suggestions on writing a trigger to enforce this business logic.

You're correct on the datatype-hurting-performance, but in this case, it's unlikely to apply. Consider this a micro-optimization, but still worth considering.