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
>

Lookup Component Stalls

i am using a lookup component to do a typical SCD. Compare the Natural keys and if they are the same -- REdirect the rows and do whatever, If not present -- means the Error Rows -- redirect and do whatever.

WHen I use the component to do a Historical Load (which means -- there are no rows are in the Destination table) and put the Memory to Partial Cache -- the Data Flow STalls after about 46,000 rows, it just doesnt complete after that. But the moment I switch it to Full Cache -- it flows -- But Partial is what I am supposed to be using -- keeping in mind -- the Incremental Loads. Why does the component stall ?

I had used Partial Cache in an earlier project -- with a 18 Million Row Table --(albeit for incremental load) and it worked fine (though is was slow -- but tleast it worked) -- but now I am trying to load just 300,000 rows but it stalls.

I am using a 2GB RAM machine -- and set the Memory to 750 MB/500 MB nothing worked

I tried two different machines -- same thing happened.

Any insight will be appreciated.

I am jut wondering why you said that the partial cache is required in the historical load. I always use (after making sure the server have enough memory available) full cache.

How is your LKup set up? you should be using the only required columns; you said this is for a SCD; so you should be uisng a query with the busines/natural key columns only (please provide the number of columns and data type of the required columns). Also, how is the source component set up? are you using 'fast load'?

|||

Hmmm so you use Full Cache always? If I leave it at Full Cache -- from next time onwards during Incremental Loads -- it will load all 290,000 rows into memory and possibly less than 10 rows have changed. I dont want to do that.

Yes I bring ONLY the required columns actually 4 columns NaturalKey, InferredMember, Hash1 and Hash2 -- all Integers

Yes I use Fast Load.

I use OLEDB for my Source.

|||

JaguarRDA wrote:

Hmmm so you use Full Cache always? If I leave it at Full Cache -- from next time onwards during Incremental Loads -- it will load all 290,000 rows into memory and possibly less than 10 rows have changed. I dont want to do that.

Yes I bring ONLY the required columns actually 4 columns NaturalKey, InferredMember, Hash1 and Hash2 -- all Integers

Yes I use Fast Load.

I use OLEDB for my Source.

An INT32 field, though (call it "natural key") will only take up 1.1MB of memory to store 290,000 rows. Not too bad, eh?|||

That is exactly my point. Even with 4 columns SSIS should be able to cache the whole result set in no time. I would recommned you to watch the progress tab in BIDS to check how long the lookup caching takes. Check also the task manager to see if there is any other process taking to much resources from the box. You can try to replace the OLE DB destination with a rowcount transformation just to test the transformation speed. You can remove the lookup and destination and use the same row count to mesaure the 'reading' speed from the source component.

Make sure you are using fast load in your OLE DB destination.