I concurr on all your points Tom.

My limitation is that I am synchcronziing data from an existing dos 
database program.

Thanks for the comments.

Peter


On 12/02/2018 12:40 AM, Tom Krieg wrote:
> 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.
>>>