By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,453 Members | 3,194 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,453 IT Pros & Developers. It's quick & easy.

Relationships Between Lookup Tables and Regular Tables

P: n/a
Do I need to create a relationship between fields in a table I created
for a lookup(combo box) and the actual table that contains the combo
box? Or do these lookup tables exist independently of the main tables?
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"Mark Lees" <ma*******@byu.edu> wrote in message
news:c4**************************@posting.google.c om...
Do I need to create a relationship between fields in a table I created
for a lookup(combo box) and the actual table that contains the combo
box? Or do these lookup tables exist independently of the main tables?

Short answer: Yes - you need to create a relationship.

Longer answer: Starting with the important point that in my experience
lookup tables are more trouble than they're worth. I'm not sure why they
were introduced - perhaps a sort of shortcut for people who didn't want to
go into too much detail designing tables? Who knows. But anyway my advice
would be to leave them alone.
For example, if you had a table two tables:

tblPersons: PsnID, PsnFirstName, PsnLastName, PsnStsID
1 - Peter - Smith - M
2 - Fred - Jones - D
3 - Sally - Carter - S

tblStatus: StsID, StsName
M - Married
D - Divorced
S - Single

Then simply setting tblPersons.PsnStsID to be looked up from tblStsID does
not do a good job. Try it. You will see that you will be able to delete
the status "S" which leaves poor Sally Carter with a status of "S" which
cannot be looked up in the status table.
Now if you had defined the relationship properly then you could make sure
that when you tried to delete "S" one of two things happened depending on
how you set up the relationship.
1. Status S is deleted - together with all people with this status. This
means you have specified cascade deletes (perhaps not too good an idea here)
2. Cascade deletes are not in effect so you are told that you can't delete
"S" because some people still have this status.
This is only one aspect of enforcing proper relationships. There are more,
but hopefully this will be anough to persuade you.
Fletcher



Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.