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

reusing lookup table in DB design

P: 13
I think I'm violating some database building principle here, but I don't know what it is, or how to work around it.

I have three tables (the example is a scaled-down version but structurally accurate):

theader -- header info about a customer
tdetail -- details about specific interactions with the customer
tanalyst -- a table storing employee names

The problem seems to be that I'm using tAnalyst as a lookup value in both tHeader and tDetail (an analyst might 'own' a customer, i.e., be stored in the header, and a totally different analyst might have some specific interaction with the customer and be stored in the detail table).

So there's a relationship between theader and tanalyst and a second relationship between tdetail and tanalyst.

This query acts appropriately, returning all the records in the detail table:
Expand|Select|Wrap|Line Numbers
  1. SELECT tHeader.MA, tDetail.CurrentAnalyst
  2. FROM tHeader INNER JOIN tDetail ON tHeader.MA = tDetail.MA;
This query returns only the detail record for the analyst on the header record:
Expand|Select|Wrap|Line Numbers
  1.  SELECT tHeader.MA, tDetail.CurrentAnalyst, tAnalysts.AnalystName
  2. FROM tAnalysts INNER JOIN (tHeader INNER JOIN tDetail ON tHeader.MA = tDetail.MA) ON (tAnalysts.AnalystID = tDetail.CurrentAnalyst) AND (tAnalysts.AnalystID = tHeader.PrimaryAnalyst);
Would greatly appreciate any insights. Thanks.
Jul 19 '07 #1
Share this Question
Share on Google+
4 Replies


hyperpau
Expert 100+
P: 184
I think I'm violating some database building principle here, but I don't know what it is, or how to work around it.

I have three tables (the example is a scaled-down version but structurally accurate):

theader -- header info about a customer
tdetail -- details about specific interactions with the customer
tanalyst -- a table storing employee names

The problem seems to be that I'm using tAnalyst as a lookup value in both tHeader and tDetail (an analyst might 'own' a customer, i.e., be stored in the header, and a totally different analyst might have some specific interaction with the customer and be stored in the detail table).

So there's a relationship between theader and tanalyst and a second relationship between tdetail and tanalyst.

This query acts appropriately, returning all the records in the detail table:
Expand|Select|Wrap|Line Numbers
  1. SELECT tHeader.MA, tDetail.CurrentAnalyst
  2. FROM tHeader INNER JOIN tDetail ON tHeader.MA = tDetail.MA;
This query returns only the detail record for the analyst on the header record:
Expand|Select|Wrap|Line Numbers
  1.  SELECT tHeader.MA, tDetail.CurrentAnalyst, tAnalysts.AnalystName
  2. FROM tAnalysts INNER JOIN (tHeader INNER JOIN tDetail ON tHeader.MA = tDetail.MA) ON (tAnalysts.AnalystID = tDetail.CurrentAnalyst) AND (tAnalysts.AnalystID = tHeader.PrimaryAnalyst);
Would greatly appreciate any insights. Thanks.
You must have two separate fields for the analyst who owns the customer, and another field for the other analysts who might have had interactions with the customer. Then you can have them both lookup to the same table where the names of the analysts are stored.
Jul 19 '07 #2

P: 13
You must have two separate fields for the analyst who owns the customer, and another field for the other analysts who might have had interactions with the customer. Then you can have them both lookup to the same table where the names of the analysts are stored.
Sorry, maybe I'm not understanding -- I have two separate fields, one, theader.primaryanalyst, and two, tdetail.currentanalyst. They both look up values in tanalyst....
Jul 19 '07 #3

P: 13
Also, this query is a better example of what I'm talking about then the first query I posted. Again, this does work -- it returns everything, with fields for both primary and current analyst. It seems like it's only when I link to the analyst table (to get the full name, instead of just the ID) that it fails (or, more precisely, I fail);

Expand|Select|Wrap|Line Numbers
  1.  SELECT tHeader.PrimaryAnalyst, tDetail.CurrentAnalyst, tDetail.MA, tDetail.StartDate
  2. FROM tHeader INNER JOIN tDetail ON tHeader.MA = tDetail.MA;
Jul 19 '07 #4

P: 13
I think I got this -- it required two queries (or possibly some intricate joining from someone more highly skilled than myself). First, a query to join the header and details together, than a query against that returned recordset to pull in the analyst names.

If anyone thinks that this reuse of a lookup table violates some data integrity rule please post. It feels kind of wrong to me, but I can't quite get at why. When I look at the relationships in the visual interface the two lines snaking into this table is a little disturbing--it doesn't have the tidy structure I hope to see when I open that window.

Thanks.
Jul 19 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.