Paradox Community

Items in pnews.paradox-development

Subject:Re: Referential Integrity
Date:Sun, 2 Dec 2018 19:40:05 +1100
From:Tom Krieg <>
Oh, and use meaningless unique identifiers (integer) for primary keys 
and do not, ever, ever, change them. That way, RI just needs to check 
whether the parent exists (creation) and do a setrange on the foreign 
key index (for deletion of child records when a parent is deleted) and 
not have to worry about updating links. There is no need to have links 
based on registration number or such-like.

Tom Krieg wrote on 1/12/2018 12:22 PM:
> 4 main tables. Customers, Vehicles, Jobs, Invoices. Invoices link to 
> customer and job. Jobs link to vehicle. Vehicles link to current 
> customer. The vehicle service records (i.e. Service Job (Job ID), 
> Service Details (Detail ID, Job ID) and Service Parts (Parts ID, Job ID) 
> should be separate from the invoice table. Invoices would reference the 
> Job ID, so you can have multiple invoices for the same job (periodical 
> invoices, for example for a restoration job). The job would be linked to 
> the customer (customer ID) AND the vehicle (vehicle ID) so you can track 
> job history by customer AND by vehicle (Vehicle ID) no matter who the 
> current customer is. You could also do what I did, have intermediate 
> tables, such as (for e.g.) :
> Record ID
> Vehicle ID
> License Plate #
> License Plate State
> Date Registration Changed
> Owner (Customer ID)
> Date Acquired
> This keeps a complete history of the vehicle, changes of registration 
> and changes of ownership. The RI portion of it is a little complex but 
> if you have a library method checking all the relationships exist it's 
> quite straight forward.
> An automotive system can get quite complex, so you need to design your 
> database carefully.
> Peter wrote on 1/12/2018 11:40 AM:
>> The transfer of ownership is the part I am currently working on. You 
>> are correct that the invoice history links has to remain with the 
>> original owner for accounting and for maintaining customer history but 
>> the there is also the need for the vehicle servicing records to follow 
>> the vehicle.

Copyright © 2004