18

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?

Improve this question
19

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.

Improve this answer
12
  • 2
    great points. It makes me wonder why others do use char(1) (like sys.objects). – Thomas Stringer Oct 20 '11 at 4:37
  • 2
    sys.objects has a legacy going back to Sybase and early versions of SQL Server en.wikipedia.org/wiki/Microsoft_SQL_Server#Genesis If you look the system objects you can see codes not IDs all over: but less so in the new DMVs. As for other folk except MS? Think RDBMS and not OO/Enums and it makes sense to use Lookups. – gbn Oct 20 '11 at 4:57
  • 1
    ok, I agree. Thanks for making that clear. So is it a pretty accurate statement that there is no real over-engineering with relational integrity and lookup tables? I mean, in this case with storing a handful of records in a lookup table and referencing them in a consuming table, would the previous statement be accurate? – Thomas Stringer Oct 20 '11 at 5:02
  • 1
    @onedaywhen: It makes a difference wether you look at the different types as data or as static set of values that never will change. If you use a type table you can just add another type to the table without the need to change the business rules in the database and in the application. – Guffa Oct 20 '11 at 7:54
  • 1
    @MichaelKjörling: Correct, if it was just a key. But OP intends it to mean something like "v" or "s". Note, we aren't on about self describing and complete natural keys like currency codes (EUR, USD, GBP, CHF etc). – gbn Oct 20 '11 at 9:45
3

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).

Improve this answer
8
  • Do you mean to allow a null code? – Jack Douglas Oct 31 '11 at 19:05
  • Yeah, code is optional to allow represent an vehicle type that is not codified yet. – Fabricio Araujo Oct 31 '11 at 19:33
  • Why people like to stay deleting comment? Is pretty annoying. – Fabricio Araujo Oct 31 '11 at 19:33
  • @FabricioAraujo - If a comment is obsolete (e.g. "I'll edit that in to my answer" and then you actually do so) then it's good to clean up the comments that no longer apply. – Nick Chammas Oct 31 '11 at 20:49
  • 1
    I would suggest adding an "Unclassified" type to your type table and making the default value of your VehTypeId field that unclassified value, rather than allowing a null value in the foreign key field. It's generally bad practice to make "null" have a valid business meaning. – Michael Blackburn Sep 23 '15 at 13:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.