Best access structure for customer / contract / scanner relationship
Posted on 2014-10-21
I'm extending an existing database (with some 15,000 records) which holds data on Customers, their Scanners and Service Reports for repairs etc. to those scanners. I want to add Contracts and a Call-Out data.
Customers can have service contracts for their scanners (typically 1 to 3) but can also have scanners outside a contract which have repairs. Some customers do not have contracts. I'm looking for the best relationship (in Access terms) between Customer and Scanner.
The contract holds data such as cost, invoice no, start/end dates, scanner details. Scanners are unique (serial no) but over time can be sold to another customer or taken out of service.
A typical arrangement:
. . . . Scanner 1
. . . . . . Contract . . . . . . Scanner 2
\ . . . . . . . . . . . . . . . . . . . . Scanner 3
I could treat the relationship between Customer and Scanner 3 as a pseudo contract (T&M) if necessary ?
I need to add/update contracts (using a form/subforms) which relate to all scanners for that customer.
This suggests a junction table for contracts but . . can all info relating to the contract (cost, invoice no) etc. be held in such a table . . or do I have a separate table for contracts creating a second linked many to many relationship.