Best access structure for customer / contract / scanner relationship

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.

Brian FieldingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Since a single scanner could belong to more than one customer over the life, and assuming you want to know the "history" of that scanner, you'd need a table to relate Scanner and Customer:


A Contract has nothing to do with a Repair or Service - it's just an Appointment (for lack of a better term). So you'd create a table that holds the Contract data:

etc etc

Assuming a Customer can have more than one Scanner under a Contract, you'd need a Join table:


You'd have to think about that one - for example, if this is an All-or-Nothing scenario, where ALL Scanners owned by the Customer would ALWAYS be under contract, then you don't need this table. If you have Customers who have some Scanners under contract, then you'll need a way to track which scanners are included in that Contract.

To track Service, you'd need a basic Service table:

etc etc

Note you'd only fill Contract_ID if the Service is part of a Contract, so you'd be able to determine all Services that are done under a Contract by querying that table for Contract_ID IS NOT NULL.
Brian FieldingAuthor Commented:
That was quick !! - Thanks

I have limited experience of Access - but years of experience in database but I can't get my head around this issue:

I need two basic tables tCustomer and tScanner (and am happy with dealing with service/repairs using another table) and a third tContract table.

I can see the need for the two join tables tCustomer-Scanner (for history and non-contract) and tContract-Scanner (for Contracted scanners)

It is theoretically possible that a customer could have 2 contracts for several scanners !

This is where my lack of understanding of MS Access shows:

Could the tCustomer-Scanner join table hold the ContractID  and have the tContract table link to this join table using the ContractID.

Thanks for your assistance.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
It is theoretically possible that a customer could have 2 contracts for several scanners !
Do you mean a Customer could have 2 Contracts for the same scanners? If so, then you'd have two sets of records in both the Contracts table, and in the Contracts_Scanners join table.

So CustomerA could have Scanner1, Scanner2 and Scanner3. All 3 of those Scanners could be covered under ContactZ and ContractX. ContractZ and ContractX would have their own independent details stored in the Contracts table. Service and such to each Scanner would be stored in the Service table, with links back to the relevant Contract_ID as needed.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Brian FieldingAuthor Commented:
CustomerA could have 2 or more contracts, each with a number of different scanners.  No scanner would be on more than one contract concurrently.   It is possible for CustomerA to have a scanner on contract for a time, then not renew the contract and then take a new contract some time later.

CustomerA could also have a non-contract scanners.

Each Scanner Service is held in tService and would be linked to (one) scanner only. A service does not cover more than one scanner.  I don't think I need a link back to the contract or customer.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If so, then the basic design I suggest would be a good place to start, I think. Using that structure, a Customer could be associated with multiple Contracts, and each Contract could be associated with multiple Scanners.

I don't think I need a link back to the contract or customer
I don't know your business process or needs, so of course it's impossible for me to second guess you - but it would seem relevant to know that ScannerA was serviced on 2014-05-01, and that scanner was associated with CustomerA during that period (and during Contract1).
Brian FieldingAuthor Commented:
Your para 1 is correct; para 2 - we need to know when the scanner was last serviced, what was done etc.  but we don't necessarily need to know who owned the scanner at that time - although in hindsight it would be useful.

I try to describe the business:

We have 300+ customers with Scanners (these are the large A0 format scanners) on Contract.  There are also a large number of customers out there who we do not know about. When a scanner breaks down, the customer will call us and hoping we will then send an engineer to fix it.

When a customer contacts us we check to see if the scanner is on contract (and not timed out) if so the engineer is sent out asap.  If the contract is out of time we prepare and send him a quote.  If the customer is not known to us, we record the company, address, scanner details etc. and prepare a quote.  On receipt of acceptance of the quote an engineer is sent out.

The engineer will repair the scanner and on return inputs a Scanner Service Report for the repair.  An invoice is then prepared and sent.  Currently, if the scanner is under contract the contract no is recorded, if not, it is treated as time and material, and the customer details are recorded.

Currently the system holds Service reports and very basic contract reports we have 3 key tables: tServiceReport, tServiceParts and tContract.  They are not linked.  We do not have a tScanner table which I am currently creating with the known scanners and preparing links to the tServiceReport and (updated) tContract.

Our idea is to have 4 key tables tCustomer, tContract, tScanner and tCallOut - fully linked.
So that: when a customer calls we input basic details and the system will tell us if he has a current contract, when we last visited, any outstanding calls etc.  It will also tell us when and what was done on his scanner(s) at the last 1 or 2 visits.  If he not on the system we will input customer and scanner details. The system will then prepare a quote (very basic).

On the engineer's return he will complete the service report, update anything needed and close the call.  Hopefully we can prepare an Invoice from this (or at least a draft invoice)

We need to hold for each Customer all Calls / Actions, Contracts, Scanners etc. and for Scanners details of repairs, parts used.
Access is no different from any other relational database when it comes to proper schema design.  The only "issue" I see with this application is the presence/absence of a contract.  To simplify the design, I would create non-contract contracts simply to make the schema consistent.  You don't want to have to have different queries for the contract/non-contract situations.  That means that every scanner is linked to one and only one contract once it is sold (assuming you are selling scanners also).

If you want to keep a history of who had what scanner when, create a junction table between contract and scanner rather than placing the contract ID in the scanner table directly.  This makes processing logic and queries a little more complex and if you want to KISS, then skip the junction table and go with the 1-m relationship.  Then you can keep history only in the "junction" table.  So each time you change the contract for a scanner, you create a history record with the start and end dates of the contract for that scanner.

Service orders will seem to break normalization rules because you should keep not only the scannerID but also the contractID.  That will keep you from having to trudge through the history junction table each time you need to report on scanner service.  Look at it this way, the relationship between scanner and contract is time dependent so if you decided to not keep history, you wouldn't have any option but to keep the contractID in the service order since scannerID would always join to the current contract and that wouldn't work for historical service orders.  I'm just suggesting that keeping both FKs will make queries easier regardless of whether you keep history or not.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brian FieldingAuthor Commented:

To Clarify:

I need tables: tCustomer,  tContract , tScanner, tService each a 'one to many' relationship.
No join tables !

I'll create non-contract contracts.

I'll add ContractID to the service record.  Is it worthwhile adding CustomerID as well ?

If this works I'll then consider history.

I'm currently working on this, and have a "test" system which I'm using to develop and get back to you in a few days.

Thanks again.
No.  I would not add CustomerID since that would violate third normal form.  It is a simple join from contract to customer unlike the join from scanner to contract where you only get the "current" contract so you can't ever see history.
Brian FieldingAuthor Commented:

Having discussed this internally and thinking about it overnight I believe the table relationships / structure is :

tCustomer   1-m   tContract   m-1   tScanner   1-m   tService   1-m  tPartUsed

In terms of History - we would look at the service reports for a scanner and this would give us sufficient information about the scanner - who owned it etc. is not relevant.

Hopefully I'll  get back to you in about 6-7 hours.

Thanks again
That works as long as the FK to tContractis also included in tService because scanners will change contracts over time.  Make a comment in the description of the FK to remind yourself and your successors why this seemingly redundant FK is included.
Brian FieldingAuthor Commented:
Have now got a test system running providing the basic functionality.
Thanks for the patience and the discussion over the solution.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.