table w/ two masters

[JBirdieH]

In the case where you have for example a job table, a customer table, and a job/customer table, can referential integrity REALLY occur for both? It seems that the cascading does not work if the primary index doesn't begin with the same as the master table. In my case, the job as a master to job/customer works (cascades), but the customer to job/customer doesn't. It can keep out postings to job/customers that are not in customer, but it won't cascade a changed name. Any ideas?

 

[footpad]

JBirdieH,

I'm afraid RI, as defined by the BDE, only works through one level. Once you need to involve more than one table, it's difficult to use the basic RI capabilities provided by BDE. At that point, you pretty much need to code the business rules.

I usually separate the fields that are affected by RI into separate tables. For example, instead of including CustomerName in your Job table, I'd only include a customer ID. Since the ID of the customer should never change, changes to their name should never be an issue.

That is, you might select a different customer, but it should never affect the other contents of the Job table.

This is also one of the reasons why normalization is a very good exercise, for it can completely remove the need for a lot of jiggery pokery.

Hope this helps...

-- Lance