I've spent the most part of the day running the SQL Enterprise Manager to design a new database today for our latest project. One table in particular stores information about lift trucks (go figure) and there's a field to specify what type of power the truck runs off of. A lift truck can run with LP, gas, electric, or dual (LP/gas) configurations. Normally i would set up a table which would store each of these values in a row and assign each one a key (usually a single character or tinyint) and then store the key value in the master table. However i remember reading a weblog post look up tables and the author said this was a horrible way to do things from a database perspective. I found others who agreed.
I never studied database design from an academic perspective. From what i've picked up over the years, I've learned to throw together tables that seem to be logical, have limited duplication of data, and store no calculated values. After reading his comments, what i really have in my table is a domain rather than a relationship. Therefore, i've coded the power source field as a varchar(8) and added the proper check constraint to only accept values of "LP", "Gas", "Electric", or "Dual". As the author suggested, I put together a view to pull the list of possible values which the web application can use to populate drop-down boxes. Because this particular application isn't that demanding, i doubt this style change will have a significant performance improvement but i'm willing to try something new if its supposed to be a "better" way of doing things.
Posted by Matthew at May 7, 2004 03:52 PM