Search code examples
sqlsqldatatypes

SQL Store varchar or int when I have only a set of values

I would like to know if it is better (subjective i know) to store an integer of values or a string of values when the field only has a set of possible values. E.g.

 Person Table
 1.
 Name Age Category
 Joe  25  0
 Jane 28  2
 John 22  1

 2.
 Name Age Category
 Joe  25  Student
 Jane 28  Teacher
 John 22  Staff

Which method is advisable? Method 1 is probably faster and better for querying, however, there is more programming cost when displaying data.

Method 2 is probably slower, more expressive and less programming cost.

Any advise will be useful.

Thanks in advance

Solution

  • You would generally do this using a reference table, with the category, and an integer for linking the tables.

    A reference table has multiple advantages:

    • The list of possible values is available in one place. This is handy, for instance, for generating a list in an application.
    • There are no misspellings.
    • You can store additional information, such as a short name, a long description, honorific, etc.
    • If you need multi-lingual support, you have all the values in a single place.
    • The same values can be shared across multiple tables.

    Sometimes, a reference table isn't appropriate. For instance, you might have just two values, ON and OFF. You can validate the values using a CHECK CONSTRAINT in most databases. That is a reasonable alternative. But I suspect that the category has more information than just a handful of values.