Lookup tables

What are the typical things you look for to decide whether to use a lookup
table?
I typically have Country lookup tables, State lookup tables, Airline lookup
tables, etc.
We also have a field that is a company type which would be one of 6 values:
Corporation
Partnership
LLC
LLP
Sole Proprietorship
Non-Profit
If I were going to set this up as a table I would either an identity field
(tinyint) and use that in my Company table or use a char(2) (C,P,LC,LP,S,N)
field.
I have another field where the only values are "FEIN" or "SS". Now every
company would have either of these but for only 4 characters (char(4)), I
don't bother putting this in a lookup table.
I am also leaning towards not having the above company type field and just
put the 2 character codes into the Company code directly.
Just curious on what others use as their criteria.
Thanks,
Tom"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:eVBOpWAsFHA.908@.tk2msftngp13.phx.gbl...
> What are the typical things you look for to decide whether to use a lookup
> table?
> I typically have Country lookup tables, State lookup tables, Airline
> lookup tables, etc.
> We also have a field that is a company type which would be one of 6
> values:
> Corporation
> Partnership
> LLC
> LLP
> Sole Proprietorship
> Non-Profit
> If I were going to set this up as a table I would either an identity field
> (tinyint) and use that in my Company table or use a char(2)
> (C,P,LC,LP,S,N) field.
> I have another field where the only values are "FEIN" or "SS". Now every
> company would have either of these but for only 4 characters (char(4)), I
> don't bother putting this in a lookup table.
> I am also leaning towards not having the above company type field and just
> put the 2 character codes into the Company code directly.
>
Another table that I am setting up (same question) is our User table where
the roles might be "Master", "Administrator","Manager","Guest", etc.
Would it be best to set this up as a lookup table with RoleID as a GUID, so
you couldn't guess it? Or I could just use some randomly generated integer
to do the same thing.
Thanks,
Tom

> Just curious on what others use as their criteria.
> Thanks,
> Tom
>
>|||Lookup tables are a good place to store all sorts of meta data (other than
just ID / Description) that can be classified by type. Typically, when
grouping items on a report or web page by type, there is a preference for
sort order, so you want to have a non-null SortCode column with a unique
constraint. If certain customers, depending on their company type, receive
direct marketing mailers or special discount offers, then those would be
implemented as a columns in this table as well.
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:eVBOpWAsFHA.908@.tk2msftngp13.phx.gbl...
> What are the typical things you look for to decide whether to use a lookup
> table?
> I typically have Country lookup tables, State lookup tables, Airline
> lookup tables, etc.
> We also have a field that is a company type which would be one of 6
> values:
> Corporation
> Partnership
> LLC
> LLP
> Sole Proprietorship
> Non-Profit
> If I were going to set this up as a table I would either an identity field
> (tinyint) and use that in my Company table or use a char(2)
> (C,P,LC,LP,S,N) field.
> I have another field where the only values are "FEIN" or "SS". Now every
> company would have either of these but for only 4 characters (char(4)), I
> don't bother putting this in a lookup table.
> I am also leaning towards not having the above company type field and just
> put the 2 character codes into the Company code directly.
> Just curious on what others use as their criteria.
> Thanks,
> Tom
>
>|||During the design phase, you might want to analyze whether this list is time
variant or not. If they are, you must use a table.
If they are static and the list is small, you may use a simple CHECK
constraint on the column. If you are using abbreviations, make sure you
document the expanded details with the actual constraint values in your data
dictionary.
Anith|||"JT" <someone@.microsoft.com> wrote in message
news:uZwwugAsFHA.460@.TK2MSFTNGP15.phx.gbl...
> Lookup tables are a good place to store all sorts of meta data (other than
> just ID / Description) that can be classified by type. Typically, when
> grouping items on a report or web page by type, there is a preference for
> sort order, so you want to have a non-null SortCode column with a unique
> constraint. If certain customers, depending on their company type, receive
> direct marketing mailers or special discount offers, then those would be
> implemented as a columns in this table as well.
>
They would have special table for these special discount offers or direct
mailers?

> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:eVBOpWAsFHA.908@.tk2msftngp13.phx.gbl...
>|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23YfKajAsFHA.2596@.TK2MSFTNGP09.phx.gbl...
> During the design phase, you might want to analyze whether this list is
> time variant or not. If they are, you must use a table.
What do you mean by time variant and why would you use a table if they are?
Thanks,
Tom
> If they are static and the list is small, you may use a simple CHECK
> constraint on the column. If you are using abbreviations, make sure you
> document the expanded details with the actual constraint values in your
> data dictionary.
> --
> Anith
>|||Time variant means that the list may change over time. If the list is
static, then a check constraint can be used to ensure that only correct
information can be stored in the database. If the list can change, then the
list should be stored in a table and a foreign key constraint should be used
to ensure that only correct information can be stored in the database.
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ufxM2qBsFHA.3216@.TK2MSFTNGP12.phx.gbl...
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:%23YfKajAsFHA.2596@.TK2MSFTNGP09.phx.gbl...
> What do you mean by time variant and why would you use a table if they
are?
> Thanks,
> Tom
>|||"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:%23vwfgGCsFHA.3340@.TK2MSFTNGP15.phx.gbl...
> Time variant means that the list may change over time. If the list is
> static, then a check constraint can be used to ensure that only correct
> information can be stored in the database. If the list can change, then
the
> list should be stored in a table and a foreign key constraint should be
used
> to ensure that only correct information can be stored in the database.
So in my examples:
Corporation
Partnership
LLC
LLP
Sole Proprietorship
Non-Profit
or
FEIN
SS
Would it be better to use a Check constraint for these (could be entered by
a dropdown list to make sure the values are correct)?
If so, should I use the long or shorter versions of the entries "C" in place
of Corporation, "P" for Partnership, "F" in place of FEIN or "S" in place of
"SS"?
Thanks,
Tom

> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ufxM2qBsFHA.3216@.TK2MSFTNGP12.phx.gbl...
is
> are?
you
your
>|||Hi tshad
If i am to use, I Use Companytype with identity instead of natural key. This
helps you when data inserted, updated or deleted. Imagine, what will happen
if you are to add another companytype say joint venture.CHECK will not help
you here.
You can use check constraints fo constant things like sex(mail,female),
blood groups which never.If you fell there would be slightest change(that ma
y
not be in the near future) in the data, better use master tables.
Regards
R.D
"tshad" wrote:

> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:%23vwfgGCsFHA.3340@.TK2MSFTNGP15.phx.gbl...
> the
> used
> So in my examples:
> Corporation
> Partnership
> LLC
> LLP
> Sole Proprietorship
> Non-Profit
> or
> FEIN
> SS
> Would it be better to use a Check constraint for these (could be entered b
y
> a dropdown list to make sure the values are correct)?
> If so, should I use the long or shorter versions of the entries "C" in pla
ce
> of Corporation, "P" for Partnership, "F" in place of FEIN or "S" in place
of
> "SS"?
> Thanks,
> Tom
>
> is
> you
> your
>
>|||It depends. If the list is long, then you may want to put it in a separate
table to make it easier to populate those drop downs. If you're considering
globalizing your application, then you should definitely separate the list
into another table, so that you can have translated lists for each supported
language. I prefer to use the longer version when I use a check
constraint, because it's less work for the application. If there's 2
billion rows, however, then performance and disk space become a factor, and
the use of a lookup table or abbreviations becomes more attractive.
"tshad" <tfs@.dslextreme.com> wrote in message
news:#vA4e$EsFHA.3240@.TK2MSFTNGP12.phx.gbl...
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:%23vwfgGCsFHA.3340@.TK2MSFTNGP15.phx.gbl...
> the
> used
> So in my examples:
> Corporation
> Partnership
> LLC
> LLP
> Sole Proprietorship
> Non-Profit
> or
> FEIN
> SS
> Would it be better to use a Check constraint for these (could be entered
by
> a dropdown list to make sure the values are correct)?
> If so, should I use the long or shorter versions of the entries "C" in
place
> of Corporation, "P" for Partnership, "F" in place of FEIN or "S" in place
of
> "SS"?
> Thanks,
> Tom
>
> is
> you
> your
>