Lookup Tables to Lookup Tables

Lookup Tables to Lookup Tables

Post by Donna S » Thu, 12 Feb 1998 04:00:00



I am on a job converting an Oracle database to SQL Server.  We are making
some changes to the data model.  The database includes about 30 lookup
tables which supply necessary data choices to the main table.  In several
cases, there are lookup tables which, in turn, supply data to fields in the
lookup tables.  I haven't come across this before and wherever the
secondary lookup tables consist of only a few choices am recommending to
the client that they eliminate the secondary lookup table and use a
constraint instead on the field in the primary lookup table.  

Are lookup tables to lookup tables standard industry practice?  (My
instinct tells me no but I thought I'd check...)

Thanks.
--
Donna S.

(Remove no-spam from address before replying!)

 
 
 

Lookup Tables to Lookup Tables

Post by Gregory Bloc » Thu, 12 Feb 1998 04:00:00


It would be helpful to see an ERD documenting the relationships between
your lookup tables, but that would be awefully hard to submit to the
newsgroup...

  Anyway, perhaps the real question should be: are the tables properly
normalized? What level of normalization does the design adhere to, and
is this level of normalization useful for your purposes? Sometimes data
model normalization can lead to some funny by-products, but I would
think that as long as the normalization makes sense and you can use it,
then there is no problem with 'lookup tables which, in turn supply data
to fields in the lookup tables'...


> I am on a job converting an Oracle database to SQL Server.  We are making
> some changes to the data model.  The database includes about 30 lookup
> tables which supply necessary data choices to the main table.  In several
> cases, there are lookup tables which, in turn, supply data to fields in the
> lookup tables.  I haven't come across this before and wherever the
> secondary lookup tables consist of only a few choices am recommending to
> the client that they eliminate the secondary lookup table and use a
> constraint instead on the field in the primary lookup table.

> Are lookup tables to lookup tables standard industry practice?  (My
> instinct tells me no but I thought I'd check...)

> Thanks.
> --
> Donna S.

> (Remove no-spam from address before replying!)


 
 
 

Lookup Tables to Lookup Tables

Post by Michael D. Barne » Fri, 20 Feb 1998 04:00:00


Donna,

    The only place that I have seen this type of design is in a many to many
relationship.  i.e. a person works for one or more companies and a company has
one or more persons.

Mike Barnes


> I am on a job converting an Oracle database to SQL Server.  We are making
> some changes to the data model.  The database includes about 30 lookup
> tables which supply necessary data choices to the main table.  In several
> cases, there are lookup tables which, in turn, supply data to fields in the
> lookup tables.  I haven't come across this before and wherever the
> secondary lookup tables consist of only a few choices am recommending to
> the client that they eliminate the secondary lookup table and use a
> constraint instead on the field in the primary lookup table.

> Are lookup tables to lookup tables standard industry practice?  (My
> instinct tells me no but I thought I'd check...)

> Thanks.
> --
> Donna S.

> (Remove no-spam from address before replying!)

 
 
 

1. Multiple Lookups to the Same Lookup Table

I have three tables that are linked together and I'm having difficulty
designing a SQL query to get the correct information.  The setup is this:

tblRollout
-----------
StoreNo (PK)
PCStatus
KitStatus
InstallStatus

tblShipStatus
-------------
ID_tblShipStatus (PK)
StatusText

tblInstallStatus
---------------
ID_tblInstallStatus (PK)
StatusText

Both the tblRollout.PCStatus and tblRollout.KitStatus fields are "lookup"
fields that reference the ID_tblShipStatus from tblShipStatus.

I can create a SQL Statement "SELECT StoreNo, StatusText FROM tblRollout
INNER JOIN tblShipStatus ON tblRollout.PCStatus =
tblShipStatus.ID_tblShipStatus" which will return the Store Number and the
"text" PC Status.  I'd like to be able to create a SQL Statement that would
return the Store Number, the "text" PC Status, the "text" Kit Status, and
the "text" Install Status from the third table.

I'm relatively new at SQL and appreciate any help you can give me!

Thanks-

Greg

2. Changing user/pwd on home-grown Java DB pooled connections

3. Lookup Behavior When Lookup Table is Empty

4. SQL 2000 active/active and SP1

5. Table Lookup - Updating all records in table

6. CONTRACT: NYC PICK Programmers Needed

7. Help -- matching data in lookup table, writing to target table

8. Export format

9. Dynamically Updating Table Lookup CTRL-SPACE tables

10. lookup tables prevent opening data table

11. DTS, Lookups--Using a lookup in a DTS transform

12. Excel text columns and lookup tables with DTS

13. UniqueIdentifer Data Type PK&FK and Access Table Lookup