Is it a bad practice to create small, one column tables to store list type
data?
like,
nameprefix: Mr., Mrs., Miss, Dr., Sir
name suffix: Jr., Sr, Esq., Md., Phd., I, II, II....
My thought is if you ever want to add to these lists you only have to do it
in the table instead of any application that uses the fields, on the other
hand the fields rarely change and you would always have to query the
database to get the values.
I know its trivial, but does anyone have an opinion on this?DAC,
Check out
http://www.windowsitpro.com/SQLServ.../5226/5226.html
HTH
Jerry
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:uHGUg0guFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Is it a bad practice to create small, one column tables to store list type
> data?
> like,
> nameprefix: Mr., Mrs., Miss, Dr., Sir
> name suffix: Jr., Sr, Esq., Md., Phd., I, II, II....
> My thought is if you ever want to add to these lists you only have to do
> it in the table instead of any application that uses the fields, on the
> other hand the fields rarely change and you would always have to query the
> database to get the values.
> I know its trivial, but does anyone have an opinion on this?
>
>|||I personally make them two column tables, with an IDENTITY column, because
if you ever have to globalize your app, you will need different information
for each supported language, so it's simpler to store the IDENTITY value in
the tables that use information in the list instead of the localized values.
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:uHGUg0guFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Is it a bad practice to create small, one column tables to store list type
> data?
> like,
> nameprefix: Mr., Mrs., Miss, Dr., Sir
> name suffix: Jr., Sr, Esq., Md., Phd., I, II, II....
> My thought is if you ever want to add to these lists you only have to do
it
> in the table instead of any application that uses the fields, on the other
> hand the fields rarely change and you would always have to query the
> database to get the values.
> I know its trivial, but does anyone have an opinion on this?
>
>|||Basically what you're talking about here is creating a domain - the set
of allowed values for a column.
To create a domain, my preferred way is to create a table and reference
that table.
This can be done with a check constraint as well, but if the allowed
values change you would have to alter the table.
I would never hard-code these into an application - too easy to get out
of synch with the domain.
DazedAndConfused wrote:
>Is it a bad practice to create small, one column tables to store list type
>data?
>like,
>nameprefix: Mr., Mrs., Miss, Dr., Sir
>name suffix: Jr., Sr, Esq., Md., Phd., I, II, II....
>My thought is if you ever want to add to these lists you only have to do it
>in the table instead of any application that uses the fields, on the other
>hand the fields rarely change and you would always have to query the
>database to get the values.
>I know its trivial, but does anyone have an opinion on this?
>
>
>|||"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:uHGUg0guFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Is it a bad practice to create small, one column tables to store list type
> data?
> like,
> nameprefix: Mr., Mrs., Miss, Dr., Sir
> name suffix: Jr., Sr, Esq., Md., Phd., I, II, II....
> My thought is if you ever want to add to these lists you only have to do
> it in the table instead of any application that uses the fields, on the
> other hand the fields rarely change and you would always have to query the
> database to get the values.
> I know its trivial, but does anyone have an opinion on this?
>
To summarize the other (on point) comments: Good practice.
David|||Agreed. Good practice.
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:uUxrf6guFHA.3756@.tk2msftngp13.phx.gbl...
> I personally make them two column tables, with an IDENTITY column, because
> if you ever have to globalize your app, you will need different
information
> for each supported language, so it's simpler to store the IDENTITY value
in
> the tables that use information in the list instead of the localized
values.
> "DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
> news:uHGUg0guFHA.2008@.TK2MSFTNGP10.phx.gbl...
type
> it
other
>|||By storing the meta data in a table, you make it easily accessable and
define what the valid options are. If you let the app developers store this
info in arrays, I swear it will be scattered in 10 different locations at
least. Also, even though you may think that the table needs only the one
column, you will probably find that other attributes can be logically stored
there as well.
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:uHGUg0guFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Is it a bad practice to create small, one column tables to store list type
> data?
> like,
> nameprefix: Mr., Mrs., Miss, Dr., Sir
> name suffix: Jr., Sr, Esq., Md., Phd., I, II, II....
> My thought is if you ever want to add to these lists you only have to do
> it in the table instead of any application that uses the fields, on the
> other hand the fields rarely change and you would always have to query the
> database to get the values.
> I know its trivial, but does anyone have an opinion on this?
>
>|||You have two choices:
1) a one-column table
2) a CHECK() constraint
Rule of thumb: If the list is long and changes a lot and the data
element is used in many places, use #1. If the list is short, fixed and
the data element is used in one place, use #2
Get a copy of SQL PROGRAMMING STYLE for the answers to these and other
questions.|||DazedAndConfused wrote:
> Is it a bad practice to create small, one column tables to store list
> type data?
> like,
> nameprefix: Mr., Mrs., Miss, Dr., Sir
> name suffix: Jr., Sr, Esq., Md., Phd., I, II, II....
> My thought is if you ever want to add to these lists you only have to
> do it in the table instead of any application that uses the fields,
> on the other hand the fields rarely change and you would always have
> to query the database to get the values.
> I know its trivial, but does anyone have an opinion on this?
Are you using these values to provide either validation or dropdown lists
for client-side data entry? If so, then you can't use a constraint.
I don't know what programming language you are using but it should be
possible to cache the data from these tables locally, eliminating the
necessity to go to the database every time. ASP.Net makes this easy. It can
also be done in ASP. If you are creating desktop apps, then it's even
easier.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
