>>>Gregory,
>>>
>>>In my case the child table does have these fields (I don't know you remember an example I described in another thread). For example, the parent table (Accounts) will have fields COST_CENT (char) and SITE_NO (int)). And the child table (Purchase orders) will have the same two fields COST_CENT (char) and SITE_NO (int). If user will change the value in the COST_CENT of the parent table, all POs where this value is set should be changed. Hence the need for the constraint with cascading update.
>>>
>>>
>>
>>if Accounts has (amongst other fields)
>>
>>Accounts_pk
>>Accounts_cost_cent
>>Accounts_Site_no
>>
>>Then, the PO table should only have an Accounts_fk, since Site_no and cost_cent can be retrieved with an sql join
>>
>>If you do not store the cost center and the site_no in the PO, then you do not need a cascading update - do you ?
>
>I see what you are saying. But this would be the case if I designed the application right in the first place <g>.
haha - there are exceptions though
I have a customer who has a tariff for certain types of work
eg, for this type of work
- charge a minimum of $150.00 including half an hour of work
- above half an hour of work, $100.00 an hour extra
The employees enter a customer number, a date, time from- to and a work code
At a certain point in time an invoice is made
I then copy the details of the tariff (minimum, including, rate above including) to the invoice line
The invoice line contains all the details of how we got to the amount billable of that line
The reason I copy ( and do not store a fk in the invoice line) is that the tariff changes say each year (as life gets more expensive)
If I look at the detail of an invoice of a couple of years back, I will see the tariffs that were applicable at that time (date of invoice)
Had I stored the fk, the calculation would not make sense
Gregory