Database Design – Choosing Between INT or CHAR for a Type Field

database-designdatatypessql server

What is the best design for a table, a Type field that is of int or char(1)? In other words, given this schema:

create table Car
(
    Name varchar(100) not null,
    Description varchar(100) not null,
    VehType .... not null
)

Is it more efficient (performance wise) for VehType to be an int or a char(1)? Say you have five types of cars, should you use the incrementing values 0 -> 4, or characters for the types (say; 'v', 's', 'c', 't', 'm')?

If it is any more than that, I'd use a separate Type table and have a foreign key relationship, but I don't see the need for that.

I notice that the sys.objects catalog view uses a character for the type field. Is there a reason for that? Am I just grabbing at thin air here, and is it whatever I'm more comfortable with?

Best Answer

You'd typically use tinyint which is 1 byte too

  • char(1) will be slightly slower because comparing uses collation

  • confusion: what is S: SUV or Saloon or Sedan or Sports?

  • using a letter limits you as you add more types. See last point.

  • every system I've seen has more then one client eg reporting. The logic of changing V, S into "Van", "SUV" etc will need repeated. Using a lookup table means it is a simple JOIN

  • extendibility: add one more type ("F" for "Flying car") you can one row to a lookup table or change a lot of code and constraints. And your client code too because this has to know what V, S, F etc are

  • maintenance: logic is in 3 places: database constraint, database code and client code. With a lookup and foreign key,it can be in one place

On the plus side of using a single letter... er, don't see any

Note: there is a related MySQL question about Enums. The recommendation is to use a lookup table there too.

Related Question