[RESOLVED]pick list vs small table

New to Web Pages, three books, still reading.  My primary table will have a "status" column.  Its content will be "open" or "Closed".  It is suggested to create a table with a primary key (statusID) for a two values table.  Is there an advantage to using
a table over a hard coded pick list with two values?’

My primary table will also have a "category" column.  It could have 4 or 5 possible values, but not 20.  Again, is there a programming/design advantage to building a table with categoryID or just hard coding a picklist?

Thanks … Dallas in Maryland


Well, yes there are advantages even if, INITIALLY you are having few possible picker values. You never know later on you could have more. And even for 4 or 5 possible values, it would be good not to store redudant text in db, instead store the IDs. The data
retrieval will be faster based on ID’s as compare to text search. And many more benifits. So I would suggest to go for table approach.

It is almost always a good idea to separate data from programming code. 

In addition, if it turns out in the future that:

(1)  Your values suddenly "expand" to include other attributes (i.e., the values have become "natural keys" to a newly discovered entity), the table and its existing relationship(s) will be there to expand

(2)  You decide to start using LINQ-to-Entities, having those values in a table (separate entity) will provide you with useful navigational properties.

Small 2 value tables, it will be.

Thanks … Dallas in Maryland 

Update:  Feb 2013 – Small tables was exactly the right approach. Each of the tables is called over and over across lots of web pages usually as a pick list.  Most of the small tables have gained an extra column or two as new pages/requirements are created. 
The benefit is I don’t have to change older pages and any way.  I simply keep the small table current.

Leave a Reply