Solved

Best access structure for customer / contract / scanner relationship

Posted on 2014-10-21
12
285 Views
Last Modified: 2014-10-27
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
                    /
Customer      
                    \ . . . . . . . . . . . . . . . . . . . . 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.

Thanks
0
Comment
Question by:Eur0star1
  • 6
  • 3
  • 3
12 Comments
 
LVL 84
ID: 40394257
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:

tScanner_Customer
----------------------------
ID
Customer_ID
Scanner_ID
Date_Received
Date_Returned

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:

tContract
-----------------
ID
Customer_ID
Contract_Start_Date
Contract_Length
etc etc

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

tContract_Scanners:
----------------------------
ID
Contract_ID
Scanner_Customer_ID

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:

tService
----------------
ID
Scanner_Customer_ID
Service_Date
Technician_ID
Service_Description
Cost
Contract_ID
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.
0
 

Author Comment

by:Eur0star1
ID: 40394373
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.
0
 
LVL 84
ID: 40394417
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.
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:Eur0star1
ID: 40394488
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.

Thanks
0
 
LVL 84
ID: 40394595
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).
0
 

Author Comment

by:Eur0star1
ID: 40394753
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.
0
 
LVL 36

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40394831
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.
0
 

Author Comment

by:Eur0star1
ID: 40394953
Thanks

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.
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40395501
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.
0
 

Author Comment

by:Eur0star1
ID: 40396251
Thanks.

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
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40397274
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.
0
 

Author Closing Comment

by:Eur0star1
ID: 40406210
Have now got a test system running providing the basic functionality.
Thanks for the patience and the discussion over the solution.

Regards
Brian
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question