Junction tables (1 Viewer)

Timberwolf

Registered User.
Local time
Today, 09:51
Joined
Dec 15, 2000
Messages
33
Is it possible to have multiple junction tables refer to the same "lookup table" for their information?

For example, I have a list of funders and a list of projects. I have a junction table of proposals that references the list of projects for which we need funding. I'd also like to create a junction table of grants received that looks up the project on the same list to which the proposals junction table refers. Is this possible?

Thanks so much!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:51
Joined
Feb 28, 2001
Messages
26,996
Yes, you can look up data from anywhere. Using the same exact table to supply lookups for two different but related processes or entities is, in fact, a good design. It will go far towards ensuring consistency - even if it happens that some elements in that table will never apply to one of the lookup users. It is OK for the table to contain some elements that don't overlap in specific application as long as they are within the format.

Don't forget to put an index on anything you need to use in a lookup. Otherwise you'll do a table scan when you should use an index scan instead. And that difference translates to speed.
 

Timberwolf

Registered User.
Local time
Today, 09:51
Joined
Dec 15, 2000
Messages
33
Thank you SO much for replying so quickly, Doc Man! Would I use the Performance Analyzer to add indexes, or is there another way to do that? I'm not finding it readily.

Thanks again.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:51
Joined
Feb 28, 2001
Messages
26,996
Presumably, your lookup table has two fields - a code field and a translation field.

Open the translation table in design mode.

Select the code field.

At the bottom of the design grid is the list of field properties.

One of the options is indexed. You can choose Unique (Primary, No Duplicates) - mandatory for a lookup
 

Users who are viewing this thread

Top Bottom