SQL Server Performance Forum – Threads Archive
Normalization & char(10) Index vs. numeric: lookup
I’ve been going rounds with our DBA trying to get signoff on a design. Coincidentally, we have this design in production on 10+ systems and it’s working great, but I’ve never worked with this particular DB. In any case, I have a combo box (or lookup) table with several different normalized "text" lookup values… The table looks like this (kind of):cboName – Char(25)
cboShort – Char(10)
cboLong – varChar(50) Name – Short – Long
——————————–
EmpStat – EMP – Employed
EmpStat – UNEMP – Unemployed
EmpStat – ST – Student
EmpStat – OT – Other (please provide description)
TypeEmp – BNK – Banking/Financial
TypeEmp – IND – Industrial
TypeEmp – whatever, you get the idea… cboName is an indexed Primary Key… Anyway, the idea is that instead of normalizing to 20 different lookup tables, we’re creating a slightly more complex structure and having simple combo boxes loaded into the a general table. We have functions that pull the data (as requested) by cboName and build strucutures in SERVER MEMORY. These strucutures are then used to create the real-time lookup to the long descriptions and create the combo boxes on the fly (when requested). The combo box table is read into memory ONE TIME as each cboName is requested, and not touched again (unless manually reset by an admin). However, I have a DB hounding me about normalization and why several different things in this table should be pulled out into their OWN normalized table. Her arguments are that this is not normalized, but I argue that it is 100% normalized. The fact that I’m "normalizing" it into a common lookup table rather than 20 separate unique tables should be irrelevant. As far as normalization goes, the repetitive data is not stored in the parent tables, the cboShort value (lookup code) is. Can anyone give me any compelling reasons for or against this that will either:
A) Convince me that a lookup table hit once per day is not the absolute most efficient way to USE this data
or
B) Give me links to good articles or websites that dispute the "100% textbook normalization approach" when the specific usage renders the DB design unnecessary (it’s read into memory)… The fact that the combo box table is only touched once per day SHOULD be enough to get her off my back, but she’s reluctant to let her separate tables approach go… Also, can anyone tell me if an indexed char(10) field is any less effective than an indexed integer field? The only real argument I think she MIGHT have is to use a numeric field as the key in the combo box table instead of the current char(25) and pull the cboName out into a parent table… But again, considerint the size of this table (< 2500 rows) and that it’s read into memory, I think that’s overkill as well… Thanks for your help!
http://www.sqlservercentral.com/columnists/dpeterson/lookuptablemadness.asp
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=132&messageid=130464
Duplicate post
Contnue discussions here
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=15184 Madhivanan Failing to plan is Planning to fail
]]>