INT or CHAR for a Type Field

Question

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?

Answer 1

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.

Answer 2

Just as an complement to great gbn's answer.

Maybe you could create something like that:

create table dbo.VehicleType
(
    VehicleTypeId int not null primary key,
    Name varchar(50) not null,
    Code char(3) null
) 
go 

create table Car
(
    Name varchar(100) not null,
    Description varchar(100) not null,
    VehTypeId int not null ,
    foreign key FKTypeOfCar(VehTypeId) referenctes dbo.VehicleType (VehicleTypeId) 
)
go 

So you can do as you please with your enum while mantaining relational integrity (using an foreign key). With the code column you can use your char codes at will, so it will become documented on the database (and you can extract the code info directly from DB, without an convoluted transformation on application code for an systems integration).