reusing lookup table in DB design

[RoseM]

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:

SELECT tHeader.MA, tDetail.CurrentAnalyst
FROM tHeader INNER JOIN tDetail ON tHeader.MA = tDetail.MA;

This query returns only the detail record for the analyst on the header record:

 SELECT tHeader.MA, tDetail.CurrentAnalyst, tAnalysts.AnalystName
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.

[hyperpau]

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:

SELECT tHeader.MA, tDetail.CurrentAnalyst
FROM tHeader INNER JOIN tDetail ON tHeader.MA = tDetail.MA;

This query returns only the detail record for the analyst on the header record:

 SELECT tHeader.MA, tDetail.CurrentAnalyst, tAnalysts.AnalystName
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.

[RoseM]

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....

[RoseM]

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);

 SELECT tHeader.PrimaryAnalyst, tDetail.CurrentAnalyst, tDetail.MA, tDetail.StartDate
FROM tHeader INNER JOIN tDetail ON tHeader.MA = tDetail.MA;

[RoseM]

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.