Paradox Community

Items in pnews.paradox-development

Subject:Re: Referential Integrity
Date:Sat, 1 Dec 2018 12:22:33 +1100
From:Tom Krieg <>
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