CHAR vs INT Design Consideration

Question

CHAR vs INT Design Consideration

Ravi-376012

SSC Veteran

Points: 232
More actions
June 2, 2010 at 1:18 am

#221693

Hello Experts,
I have a situation I need to decide on the design of my table structure. I have a table to hold my contacts the structure is like
My Contact table will look like this primarily
ContactID BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY,
FName VARCHAR(50) NOT NULL,
LName VARCHAR(50) NOT NULL,
Address1 VARCHAR(255),
Address2 VARCHAR(255),
----,
StateID INT,
.....)
Now I have two options one is to have a State lookup table using
StateID INT,
StateAbbr VARCHAR(2),
StateName VARCHAR(25))
Now I have two choices I can as well save StateAbbr directly in Contact table and create the State look up table with StateAbbr as primary key and get rid of the StateID and the second option is as shown above where I will have StateID in contact table reference to StateID in State lookup table.
If I am using StateAbbr column directly in contact table in some cases where I need to filter on the StateAbbr column I can avoid a Join to State table only case when I would need to query state table would be when I need to display StateName in full in couple of reports.
So I request experts review and advise.
Thanks in advance
-Prasad

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

Ravi-376012 (6/2/2010)
ContactID BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY,
FName VARCHAR(50) NOT NULL,
LName VARCHAR(50) NOT NULL,
Address1 VARCHAR(255),
Address2 VARCHAR(255),
----,
StateID INT,
.....)
Now I have two options one is to have a State lookup table using
StateID INT,
StateAbbr VARCHAR(2),
StateName VARCHAR(25))

this is a good approach to keep data seperately

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 2

This really gets down to personal preference to a large degree. Here's the question, will that two character abbreviation EVER change? If a country named the United States of Ted suffered a coup and was renamed the Democratic Republic of Bob, will the abbreviation of UT for the US of Ted need to be changed to DB for the DR of Bob? If so, have fun with all the work you'll need to do across your system to keep your referential integrity intact.

Personally, I go with the artificial key and don't worry about the fact that the US of Ted has such an unstable government. Changes to descriptors can be made without affecting the key.

Also, doing this to avoid a join is a poor design decision.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 3

A little side comment that is not directly related to your question. How many contacts will you have? I mean, BIGINT? Really? And INT for state id? I have never heard of two billion states. Have you? 😉

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Do not do varchar(2) for state. It is 2 and only 2 characters, so char(2) is more efficient. I would also use this as the actual identifier for state and not a surrogate since it is so small and would likely eliminate your having to join to get the 2 characters for representation.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service