Subject: | Re: Referential Integrity
| Date: | Sat, 1 Dec 2018 12:22:33 +1100
| From: | Tom Krieg <REMOVEtomkCAPITALS@sdassociates.com.au>
| Newsgroups: | pnews.paradox-development
|
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.
>
|