Solved

Best access structure for customer / contract / scanner relationship

Posted on 2014-10-21
12
281 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 34

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 34

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 34

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

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…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now