Relationships

[lefty2cox]

What is a Contact vs a Client/Customer? Does a Customer (or whatever you decide) have to have at least 1 repair job/order?

Negative. Looks like I'll have contacts without equipment and ticket/job. As I have it set up, a contact is anyone, including supplier, employee, or anyone else. Is what you're saying is that maybe I should have customers separated from the others?

Have you tried to "work your current proposed structure" with some sample data and scenarios?

Yes. I have a lot of blanks since most customers don't have companies and suppliers don't have equipment or tickets. That sounds bad now. I think I'm getting it. So the relationship between the contact table and equipment table should mean that all contacts should have equipment? So I should have a separate customer table from other types of contacts?


I haven't done anything else except design tables and relationships. I've learned from past projects where it was too late to efficiently adjust. I want to get this somewhat organized before going too crazy with the other stuffs.

[Micron]

I'd say most of the time, there is one company but several contacts within that company. What will distinguish one from the other is often a specific job, specific equipment or perhaps a PO. I think it could be said that if you put all the tables and relationships in place without first nailing down the process and who fits where, that you're putting the cart before the horse.

[orange]

I wasn't saying anything pro or con relative to your evolving structure. I was asking some questions in hopes of clarifying what you are working with. In some applications a Customer is someone(or company) that has made at least 1 purchase. in others that "rule" isn't appropriate and for those applications a Customer doesn't have to have at least 1 purchase. As micron mentioned Contacts are often people within organizations that you deal with or could deal with in some capacity. You may want to have coordinates(phone/email) for the Service Department at the XYZ Auto Store, or the website for Donyboy73. It all depends on what you need for your business.

Your concern about Equipment and Contacts and Tickets is interesting, but should not be a major concern to you at this point. There is nothing that says every table has to be intimately related to every other table. How one table ("Thing"/Entity) relates to another is all based on your Business rules/facts. There is nothing in general that dictates a Contact must have a Ticket. Now as you get more details about your business and its processes, that may become a fact, and would have to be accommodated in your database structure.

The data model, test scenarios and test data will help you refine the model and detail your business facts. You're really in the analysis stage, and the more questions and answers you can prove or eliminate by means of an evolving data model, the better will be your blueprint for when you build that physical database.

Access is a rapid development tool. So you can build simple structures to represent your draft concepts and test same with sample data. But don't start by saying/thinking I'm going to build the one and only database that is fully operational from the get go. It doesn't work like that- regardless of what M$oft and other software suppliers may say.

Build the model and work from that. If you know something is required, but don't yet have the details, then make it a black box on the diagram and label if (need to resolve Process X). The key is to identify what you need to support the business, then develop based on your priorities. The black box is helpful since you know where it fits generally, and you can expand on it with time. Worst is to have a mediocre physical database with loads and loads of detailed data only to find it doesn't support your business, or doesn't fit with some aspect of your business. More than once I've heard --I'm too deep into it now to correct things. And then there's --geez I wish I had used more test data and scenarios, I never thought of....

Enjoy the analysis and design processes.

[lefty2cox]

This is so frustrating...yet fun at the same time. I can see, based on the advice, that I've failed on step one so maybe this will be a regrouping and elaboration.

I repair equipment for customers. I need parts to do it. I keep some parts on hand, whether I purchased 5 when I only needed 1, or maybe took some off of a parts machine. I order the other parts from different places. I pay cost and charge a markup (or at least I should).


Customers own more than 1 piece of equipment and I sometimes repair more than one piece at a time for a customer. Sometimes I pick them up, drop them off, or both.


I'd like to give an invoice that shows what the charge is for parts and labor to the customer and track who has paid.


I'd like to be able to purchase parts not related to any job or piece of equipment to either keep on hand, use on a job, or sell. I'd like to track that as well.


I will likely acquire equipment to keep and possibly repair/sell or just sell. This is not something I'm actively pursuing but is likely something that will happen as people abandon / give me stuff or I come across a lawn mower on the side of the road. I'd like to track cost, repair cost and labor on these, as well as sale price.


As far as employees...I don't plan to have any in the near future or maybe even ever.


(The following idea may be easier to track in a different database but I'm not sure. I don't want to let it slow me down though)
I have a friend that has access to equipment. He'll purchase a piece of equipment and give it to me to repair. We'll then sell it and split the profit. Sometimes he'll purchase the parts and sometimes I will. In the end, we take the net and split it. It would be nice to track who has paid for what for reimbursement after the sale.


Right now, the only thing I really do is take in one or two pieces of equipment at a time, fix them with parts purchased by me, and deliver the repaired machine back to the customer. I charge them for parts and labor. Anything beyond that is tracked in my head and on paper right now. But I can see how that could become cumbersome if things ramped up even a little.

Does this help or am I missing it?

[lefty2cox]

So my 2 cents since I suggested the overview...by no means all encapsulating and certainly untested. I would normally write these out on a sheet of paper, draw lines for the relationships, pencil in the field data types and think about whether or not it supports my goals. You certainly could embellish this but given that it's kind of a hobby and not a corporate thing as I first thought, I tried to balance simplicity with function. In case it's not known, PK is primary key (I'd use autonumber field) and FK is the foreign key value, which of course is the PK from the parent table. I might even leave out the equipment table since this isn't about corporate or personally owned assets. Again, not vetted....

tblCustomer
CustPk - autonumber PK
FName
LName
Phone
etc - attributes of the customer only

tblEquipment
EquipIdPk
EquipType
Make
Model
Serial
etc

tblJobs
JobIdPK
CustIDFk
EquipmentIdFk
Notes (operating complaint or customer service request)

tblParts
PartIdPK
JobIdFk
Qty
PartNum
Description
Cost
Price

tblJobLabour
LabourIdPk
JobIdFk
LbrHours
LbrRate

Quick question on this as I try to wrap my brain around it. The relationship between tblJobs and tblJobLabor looks 1 to 1. Wouldn't that mean that labor hours and rate would end up as part of the jobs table?

[Micron]

The relationship between tblJobs and tblJobLabor looks 1 to 1

Why? What is a job - change oil? Or change oil and change spark plug and replace drive belt thus one job and 3 labor records? Or is it it one job and 4 labor records because it took 4 phases/days? That's for you to decide.

I subscribe to the notion that 1 to 1 relationships usually indicate that something should be in one table, but the nature of the business that the db supports is the main factor IMO. You could even put labor into the job table as hours or rate or amount, but if you're going to have multiple records with labor values, then you'll end up repeating all the data in the other fields for these additional labor record values. That's a sign that labor doesn't belong there. IMO, lumping things together like that will sometimes work for a while, until you decide to change the process. All of a sudden you're wishing you split them in the first place. In this case I think it will be a conscious decision rather than shortsightedness.

[lefty2cox]

I get it now. So if I use just a total labor time for the whole job then no table needed. But if I'm going to itemize labor performed for each task in the job, then it makes more sense as it's own table to avoid repetitive data in the same record? Is that close?

[Micron]

I'd say yes. In addition, I'm saying IF you split and never have more than one record per task/job then no harm done. If you don't split and then decide you need to, you'll have some work to do.

[lefty2cox]

Understood. Thank you. Regarding my original question with CustID as a foreign key in the invoice table: What is the benefit of that given that there is already a link back to the customer through ticket and then equipment? Is it just to make it easier to access the customer info when building forms and tables? Or is it necessary?

[Micron]

Answered in posts 2 and 5, albeit different views. However, did you follow the 'customers and job's link given to you? If you did, then you can see which of 2 and 5 agrees with what's shown in that schema.

[orange]

Can you post your latest tables and relationships, or a copy of your current database?
Perhaps we (collectively) can create some test scenarios and data and test your model.

Regarding your question

The relationship between tblJobs and tblJobLabor looks 1 to 1. Wouldn't that mean that labor hours and rate would end up as part of the jobs table?

Consider this analogy:
A Customer creates an Order. The Order is for 2 widgets @$3.00 each, 6 XW Clamps @$5.00 each and 1gal of Varsol @$9.50.

Typically these "Items" will be individual records (orderLineItems) in a separate table that is related to the Order table.
1 Order may have 1 or Many orderLineItems.

Is this concept applicable to your business?

[lefty2cox]

Here's the latest. I've simplified a little. I may have gotten carried away before...as well as screwed it up now.

I've decided that a ticket or "job" is for one piece of equipment. If I get more than one from the same customer on the same day, they'll be separate tickets. The likelihood of getting two of the exact same equipment on the same day s slim to none. If I do, I can tell them apart by serial.

I've given myself two options for charging a customer for labor: By hour or by service. This is typical. Most of the time it will be by hour but there are some flat fee services such as "tune up" "oil change" and such which are typical for the industry from my experience. So I have both options.

I'm a little confused as to whether to put the details regarding the parts ordered in the "tblpartorder" or in the "tblPartsInOrder". I did it this way so I can order multiple parts for a job or parts for multiple jobs at one time. This serves mainly to order and associate parts for jobs and not so much as a purchase order for a particular supplier. Set up the way it is, it would not likely be good as a P.O. I think because supplier is in the table. I haven't gotten to invoicing or inventory yet. I think with invoicing, I'll go with CustID just in case I end up selling parts without equipment...I think.

[Micron]

I would not bother with a Make table. Manufacturer is just an attribute of the entity "equipment" IMO. You're starting to lose me on the parts thing. Nonetheless I can't see how any parts table relates to the ticket if the ticket can have multiple jobs. The parts relate to the job? Do you really want to differentiate parts on order vs parts from your stock, having to nullify/move data in/between tables when a part comes in? Maybe just show a DateReceived field in a single parts table? If it's not null, you have it?

[orange]

lefty2cox

Attached in zip is the relationships window I mentioned. It focuses on the WorkOrder (could be your Ticket or Job??).
It does not deal with Invoicing per se, but does have some billing/payment info. It does not address your ordering of shop parts for general use (oil, grease, carb fluid, WD-40 type things, shop rags...).
As regards the sample forms, they are for general info.
Keep us apprised of your status.

[lefty2cox]

I would not bother with a Make table. Manufacturer is just an attribute of the entity "equipment" IMO. You're starting to lose me on the parts thing. Nonetheless I can't see how any parts table relates to the ticket if the ticket can have multiple jobs. The parts relate to the job? Do you really want to differentiate parts on order vs parts from your stock, having to nullify/move data in/between tables when a part comes in? Maybe just show a DateReceived field in a single parts table? If it's not null, you have it?

I've decided to have the ticket be the job. So there will only be one piece of equipment on a ticket. The part order may have parts for multiple jobs/tickets. So I added the join table partsInorder to delineate between them. In that context, does it look ok?


As far as the parts ordering and inventory. I just know I want to order parts. And I want to use most of them for the job I'm working on. And I want to order parts for stock and keep track of it so I can use them on other jobs later on. It's the logistics of how best to set it up, that I'm struggling with.