Clever Geek Handbook

What's the best datatype for a 1 character code column in an SQL table?

it is not a primary key, but is simply used as a marker in the existing system that marks these items with a 1-letter code, ie "N" for new, "R" for return, etc. I was about to go with varchar (50) as the datatype, but wonder if there is anything wrong with saying varchar (2), aiming for efficiency instead. THH!

0


source to share


6 answers


The best data type would be char (1).

varchar means the width of the variable , that is, if you only store 3 characters on varchar (50), there will be no 47 wasted bytes. But when accessing it the (small) result, since it has to check the actual field length for each row.



If this requirement changes later, you can change the table and change the data type without too much trouble.

+10


source


I would think char (1) would be perfect



+4


source


I would just use Char (1).

+2


source


char (1) if it is always 1 character.

If you don't see a good reason that it should be extended in the future - and even then you can look at the design and use a surrogate key for the lookup table with your varchar (50) or whatever.

0


source


I agree with @Cade Roux - if you are considering having more than 26 write states then consider converting that to FK into a state table. Also gives you the option to convert this to a data driven application eg. by adding labels to the state table rather than hardcoding them into the application.

0


source


And just to add a different opinion to the mix: depending on the situation, the alternative is to create a type table ("product_status" or something like that):

CREATE TABLE Product_Status (
    status_id INT NOT NULL PRIMARY KEY, 
    description VARCHAR(50) NOT NULL
)
INSERT INTO Product_Status (status_id, description) 
    VALUES (1, 'New')
INSERT INTO Product_Status (status_id, description) 
    VALUES (2, 'Return')
--etc.

Then you can use an int column in the original table to connect to that table using a foreign key.

ALTER TABLE OriginalTable 
  ADD status_id INT NOT NULL REFERENCES Product_Status(status_id)

There are benefits to any approach. A char (1) is smaller and is probably more readable in this situation, but a specialized table gives you a little more visibility and possibly easier management (if you want to add and remove the possible values ​​of this field later). In this case I would personally went with char (1) as people suggest, but if it's less obvious this is the route to consider.

Anyway, if you are using char (1), it is a good idea to put the column constraint there to make sure no invalid values ​​are found in:

ALTER TABLE OriginalTable ALTER status 
  ADD CONSTRAINT [Check_Valid_Values] 
    CHECK status in ('N', 'R' /* etc ... */)

A small tiny fine can now save you a huge data corruption headache later.

0


source






More articles:


All Articles