Question Help with deciding best relationship for 3 tables (1 Viewer)

Modify_inc

Registered User.
Local time
Today, 11:22
Joined
Mar 25, 2013
Messages
20
Hello all!
I'm trying to create a simple database to keep up with my few clients I provide service for. I'm trying to figure out the best way to relate my three tables I created: tblClients, tblDevices and tblJobs.

The way I see it is, Clients own Devices that require Jobs to service them, so at first I thought the tblClient would relate to tblDevices, and then tblDevices could relate to the tblJobs. But then I realized, not every client needs a job done on a device. Sometimes a client just needs consultation or other service that does not involve a device. What is a fairly simple way to resolve this as I'm still learning Access? Should I just add a non-device field entry to tblDevices or could I resolve this by using a Left Join type or any other ideas?
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 11:22
Joined
Oct 17, 2012
Messages
3,276
Honestly, the first step you may want to take is to just write down every field you intend to track. Right off the top of my head, I can see a need for tables for:

Clients
Services
WorkOrders
WorkOrderItems
Devices

What about expenses? Purchase orders/supplies? What if a client moves, do you want the old invoices to show the old address or the new one?

Figure out what you need to track first, THEN make the tables.
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:22
Joined
Jan 23, 2006
Messages
15,364
I agree with Frothingslosh that writing things down would help, and his suggested tables make sense.
I would also suggest that using in plain, simple English --not database tech talk -- you describe to us a day or week in the business. We need to hear more about Jobs and Services and Consultation and how they inter-relate. Relationships are a reflection of business facts/rules ---they are not arbitrary lines drawn between tables/boxes.
Good luck.
 

Modify_inc

Registered User.
Local time
Today, 11:22
Joined
Mar 25, 2013
Messages
20
Honestly, the first step you may want to take is to just write down every field you intend to track. Right off the top of my head, I can see a need for tables for:

Clients
Services
WorkOrders
WorkOrderItems
Devices

What about expenses? Purchase orders/supplies? What if a client moves, do you want the old invoices to show the old address or the new one?

Figure out what you need to track first, THEN make the tables.

Thanks for the reply!

I have already done the first step. I have it all laid out in Excel. I'm just trying to figure out what is the best way to relate them now in Access. Also, I'm not worried about the financials and such, as I have other software for that. I'm just trying to design a simple database to keep track of the actual work being done for each client. For my basic needs I think those 3 tables are core to my database, I'm just not sure how you relate a table that may not be used all the time (tblDevices).

I'm thinking I could use a Left Join on the tblDevices, that would show all the clients and then only the devices assigned. I could then relate the tblClients to the tblJobs using a Inner Join. But then I'm concerned that I will not be able to query jobs for specific devices, as devices will have no relationship with jobs. Or is it possible by the transitive relationship of C (Clients) = J (Jobs) and C (Clients) = D(Devices), will D = J, so I can query jobs based on devices?
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 11:22
Joined
Oct 17, 2012
Messages
3,276
You set the join type in the query, not the relationships. The entire point of the standard one-to-many join is that the child (many) requires the existence of a parent (one), but not the other way around.

In fact it HAS to be that way, because the database itself still has to load data into one table first. As an example, you create the invoice first, THEN start adding line items to it.

It sounds like you're seriously, seriously overthinking this. Watching this video may help: https://www.youtube.com/watch?v=mEkOWAEF-nQ

I was going to point you to a really good website by Chip Pearson, but it is returning nothing but 404 errors all of a sudden. :(

Also, step back and re-read what jdraw said. Your relationships have to mimic your business processes. There's more to them than just connecting the boxes, and if you don't set them up well, they'll actually end up handicapping you.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:22
Joined
Feb 28, 2001
Messages
27,003
tblClients, tblDevices and tblJobs

Clients own devices. Clients ask for jobs. Some jobs relate to devices. Some jobs relate to no devices.

Sounds like Clients is an independent table (potential parent). Both Jobs and Devices are dependent tables (potential children). They parallel each other, particularly if a job can be for more than one device as well as being for NO device.

Jobs will point back to the client.
Devices will point back to the client.

What I might do for the job is to have a JobDetails table (child of Jobs) that links back to a master Job entry (always) and optionally links to the Device if it is that type of job, but the device link can be blank for a consultation. Maybe. This JobDetails table gives you the ability to service multiple devices plus have a consultation all in one job. (If your business model allows that to happen, of course...)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:22
Joined
Feb 19, 2002
Messages
42,986
I think those 3 tables are core to my database
You need at least four tables if one job can service multiple devices. In a relational database, you use a junction table to implement a many-to-many relationship. A Job might service multiple devices and each device might be serviced by multiple jobs.

If a job is only for a specific, single device, then 3 tables would suffice because the relationship between Jobs and devices would be 1 device - to - many jobs and so the device being serviced could be stored in the job table.
 

Users who are viewing this thread

Top Bottom