Service Ticket Table(s) (2 Viewers)

[wmix]

In our quest to update/improve our current database I am looking for thoughts on best practice please.

We break things into three different categories: Machine, Property, or Management Company, and I track everything in one table (below) tblService.
The reason it was set up this was because originally, we were trying to track issues with individual machines. However, we've started tracking more information and I know I need to change this Service table into multiple tables, but I'm not sure the best way.

Here's examples of things that come up, suggestions on better way to track things is appreciate.

Example # 1
The drink machine in building 1400 at Rosewood Apartments goes down. I can get multiple calls from residents asking for refunds and I create a service ticket for each and every call or email. Then when a tech goes out and fixes the machine, I track the resolution in each and every service ticket (a lot of copy/paste) and if that person/apartment was given a refund and how much.

I'm thinking it would be nice to streamline the repair information as one "repair ticket" then associate that repair ticket with multiple refund requests with the resident's name/apartment/refund amount, etc.

Example # 2
The Vending Relations Manager from ABC Management calls and asks about adding snack machines at three different properties. I create a service ticket for each individual property, under the "main" machine at the property (like the office machine - not the best way to do it, but it works for now). Then after the owner goes and talks to the manager at the property, we record the details of the meeting. If it's decided to add machine(s) we create a new machine in the tblMachine and then "move" the association from the "main" machine to the new machine that was created.

I'm thinking it would be better to have a general "inquiry ticket" and then associate that with multiple properties. Then have a detail ticket that refers back to the initial inquiry as well as the property and any individual machines that get installed. Guessing junction tables of some sort might need to be involved.

Example # 3
Property manager calls because the property was vandalized over the weekend and the glass in the snack machine in building A was smashed and products removed, the drink machine in building A was tipped over and in building C it looks like they tried to pry the door open on the drink machine, but they were unsuccessful. (real call, just happened). So again, we track all this under each individual machine like in Example 2.

Here's how the table is currently set up:

tblService
ServiceID (PK)
MachineID (FK) (only 1 machine can be associated to the service ticket)
ContactedDate
ContactedTime
ContactName
ContactPhone
ContactEmail
ContactLocationInfo (apartment number, suite number, staff information like Manager, GM, Head of Maintenace, etc.)
ContactRequest (notes on why they called)
RefundRequestAmount (if they are requesting a refund, we track that in this currency box)
RepairDate
RepairTech
RepairNotes
MachineVandalized (yes/no)
VandalismCost
Refund Type

 

[plog]

First, read up on normalization:

Database normalization - Wikipedia

en.wikipedia.org

That's the process of setting up tables and fields properly. Then google a few tutorials and work thru them. Next, apply what you learn to your data and set up what you think is the correct tables/fields. After that post your sample database back here and we can help you work through your structure and help you get the proper one.

In general 'things' become tables and attributes/characteristics of those things become the fields in that table. To get you started, let's just take just example 1 and break it down:

The drink machine in building 1400 at Rosewood Apartments goes down. I can get multiple calls from residents asking for refunds and I create a service ticket for each and every call or email. Then when a tech goes out and fixes the machine, I track the resolution in each and every service ticket (a lot of copy/paste) and if that person/apartment was given a refund and how much.

Things = machines, apartment complexes, residents, refunds, repairs. Those are the candidates for your tables. Then just go at them one by one to determine their fields:

refunds = refund date, refund amount, customer refunded, machine/repair that caused refund, etc.

apartment complex = address, manager name

etc. etc.

I'd start with pen and paper or Excel and make a list of tables and then their fields. Then think of scenarios and see if your mocked up tables/fields will be able to hold all the data that scenario will generate. If not, add tables/fields so that it will. Once you think you have it, actually make the database then post it here so we can go over it.

Again though, I'd read up on normalization first and foremost.

 

[mike60smart]

In our quest to update/improve our current database I am looking for thoughts on best practice please.

We break things into three different categories: Machine, Property, or Management Company, and I track everything in one table (below) tblService.
The reason it was set up this was because originally, we were trying to track issues with individual machines. However, we've started tracking more information and I know I need to change this Service table into multiple tables, but I'm not sure the best way.

Here's examples of things that come up, suggestions on better way to track things is appreciate.

Example # 1
The drink machine in building 1400 at Rosewood Apartments goes down. I can get multiple calls from residents asking for refunds and I create a service ticket for each and every call or email. Then when a tech goes out and fixes the machine, I track the resolution in each and every service ticket (a lot of copy/paste) and if that person/apartment was given a refund and how much.

I'm thinking it would be nice to streamline the repair information as one "repair ticket" then associate that repair ticket with multiple refund requests with the resident's name/apartment/refund amount, etc.

Example # 2
The Vending Relations Manager from ABC Management calls and asks about adding snack machines at three different properties. I create a service ticket for each individual property, under the "main" machine at the property (like the office machine - not the best way to do it, but it works for now). Then after the owner goes and talks to the manager at the property, we record the details of the meeting. If it's decided to add machine(s) we create a new machine in the tblMachine and then "move" the association from the "main" machine to the new machine that was created.

I'm thinking it would be better to have a general "inquiry ticket" and then associate that with multiple properties. Then have a detail ticket that refers back to the initial inquiry as well as the property and any individual machines that get installed. Guessing junction tables of some sort might need to be involved.

Example # 3
Property manager calls because the property was vandalized over the weekend and the glass in the snack machine in building A was smashed and products removed, the drink machine in building A was tipped over and in building C it looks like they tried to pry the door open on the drink machine, but they were unsuccessful. (real call, just happened). So again, we track all this under each individual machine like in Example 2.

Here's how the table is currently set up:

tblService
ServiceID (PK)
MachineID (FK) (only 1 machine can be associated to the service ticket)
ContactedDate
ContactedTime
ContactName
ContactPhone
ContactEmail
ContactLocationInfo (apartment number, suite number, staff information like Manager, GM, Head of Maintenace, etc.)
ContactRequest (notes on why they called)
RefundRequestAmount (if they are requesting a refund, we track that in this currency box)
RepairDate
RepairTech
RepairNotes
MachineVandalized (yes/no)
VandalismCost
Refund Type

Can you upload a copy of your database?

 

[LarryE]

In our quest to update/improve our current database I am looking for thoughts on best practice please.

We break things into three different categories: Machine, Property, or Management Company, and I track everything in one table (below) tblService.
The reason it was set up this was because originally, we were trying to track issues with individual machines. However, we've started tracking more information and I know I need to change this Service table into multiple tables, but I'm not sure the best way.

Here's examples of things that come up, suggestions on better way to track things is appreciate.

Example # 1
The drink machine in building 1400 at Rosewood Apartments goes down. I can get multiple calls from residents asking for refunds and I create a service ticket for each and every call or email. Then when a tech goes out and fixes the machine, I track the resolution in each and every service ticket (a lot of copy/paste) and if that person/apartment was given a refund and how much.

I'm thinking it would be nice to streamline the repair information as one "repair ticket" then associate that repair ticket with multiple refund requests with the resident's name/apartment/refund amount, etc.

Example # 2
The Vending Relations Manager from ABC Management calls and asks about adding snack machines at three different properties. I create a service ticket for each individual property, under the "main" machine at the property (like the office machine - not the best way to do it, but it works for now). Then after the owner goes and talks to the manager at the property, we record the details of the meeting. If it's decided to add machine(s) we create a new machine in the tblMachine and then "move" the association from the "main" machine to the new machine that was created.

I'm thinking it would be better to have a general "inquiry ticket" and then associate that with multiple properties. Then have a detail ticket that refers back to the initial inquiry as well as the property and any individual machines that get installed. Guessing junction tables of some sort might need to be involved.

Example # 3
Property manager calls because the property was vandalized over the weekend and the glass in the snack machine in building A was smashed and products removed, the drink machine in building A was tipped over and in building C it looks like they tried to pry the door open on the drink machine, but they were unsuccessful. (real call, just happened). So again, we track all this under each individual machine like in Example 2.

Here's how the table is currently set up:

tblService
ServiceID (PK)
MachineID (FK) (only 1 machine can be associated to the service ticket)
ContactedDate
ContactedTime
ContactName
ContactPhone
ContactEmail
ContactLocationInfo (apartment number, suite number, staff information like Manager, GM, Head of Maintenace, etc.)
ContactRequest (notes on why they called)
RefundRequestAmount (if they are requesting a refund, we track that in this currency box)
RepairDate
RepairTech
RepairNotes
MachineVandalized (yes/no)
VandalismCost
Refund Type

It sounds like:

1. You work for a property management company that manages multiple properties.
2. Each property could have multiple separate buildings.
3. Each building could have one or more service request types.
4. Each service request type could have multiple service requests over time (vending machine maintenance, broken glass replacement, floor repairs etc.)
5. Each service request type could have one or more technicians associated with it over time.
6. Each technician could perform one or more services resulting in a service result.

Just reading the post, that's how I see the design hierarchy at first blush:

1. A Property table (property name, property address, manager name, manager phone etc.)
2. A Building table (building address, building name and/or number)
3. A ServiceRequestType table (vending machine, building repair, window replacement etc.)
4. A ServiceRequest table (requesting party, request date, technician dispatch date)
5. A Technician table (name, contact phone, company name etc.)
6. A ServiceResult table (result date, result description (repaired, replaced, removed, refunded money etc.)

Does this make sense to you?

 

[RonPaii]

I would also add a Complaint table. The OP stated that the service request for 1 machine may come from multiple customers.

 

[LarryE]

I would also add a Complaint table. The OP stated that the service request for 1 machine may come from multiple customers.

I thought about that, and I considered it a new customer service request with a different date and party.

 

[wmix]

Thanks for the suggestions so far. I've started moving the blank tables into a new database for the new owner of the company. Here's an example, it's not even close to complete (no forms or query's etc.). This is just a start, as the new company owner will not get all the data we currently have but he will get some.

I did start several types of service / request tables, I believe I could somehow split these up better.

I'm very willing to listen to suggestions.

Thank you everyone.

 

[LarryE]

Thanks for the suggestions so far. I've started moving the blank tables into a new database for the new owner of the company. Here's an example, it's not even close to complete (no forms or query's etc.). This is just a start, as the new company owner will not get all the data we currently have but he will get some.

I did start several types of service / request tables, I believe I could somehow split these up better.

I'm very willing to listen to suggestions.

Thank you everyone.

What business are you in? Are you a vending machine company or a property management company or what? That will determine what tables you need and how they might be associated? Can you describe exactly what the business does? I thought you were a property management company, but maybe not. The business workflow process is what determines what tables and fields are needed.

 

[plog]

What happened in the last 6 hours? You started this thread with one table (tblService) with 16 fields and now you've laid out a 49 table database that tracks everything?

What's the real story here? Had a guy build a comprehensive database a few years back, he left and now you're the guy stuck making it work? Or are these all the new tables you think you will need? Really confusing from your initial post.

 

[wmix]

What business are you in? Are you a vending machine company or a property management company or what? That will determine what tables you need and how they might be associated? Can you describe exactly what the business does? I thought you were a property management company, but maybe not. The business workflow process is what determines what tables and fields are needed.

We are a vending company. We own and operate vending machines. The database tracks the vending machines that we are the "full-service" vending company for, so we fill them, collect the money, and have revenue share (commission) with some of the properties or management companies we work with.

The Property is where the machine(s) are located
The Machine is the actual machine on location
The Management Company is the company that currently manages the property (in some cases they own the property also, but usually they do not).

 

[wmix]

What happened in the last 6 hours? You started this thread with one table (tblService) with 16 fields and now you've laid out a 49 table database that tracks everything?

What's the real story here? Had a guy build a comprehensive database a few years back, he left and now you're the guy stuck making it work? Or are these all the new tables you think you will need? Really confusing from your initial post.

Sorry for the confusion.

We have an Access Database. The company is being sold, the new owner is only getting some of the data. I have to move specific data over to a new database for the new owner. In my creating the new vending database I'm trying to cleanup/fix some of the old tables I have. Many were created "on-the-fly" as I was learning Access and they are garbage and need to be cleaned up. Since we are starting with essentially a new database I thought now would be the perfect time to start normalizing and cleaning things up.

The service table is a large part of the database, and the new owner gets the data. Before I just moved everything over, I was trying to figure out if moving forward there would be a better way to store the data.

 

[LarryE]

We are a vending company. We own and operate vending machines. The database tracks the vending machines that we are the "full-service" vending company for, so we fill them, collect the money, and have revenue share (commission) with some of the properties or management companies we work with.

The Property is where the machine(s) are located
The Machine is the actual machine on location
The Management Company is the company that currently manages the property (in some cases they own the property also, but usually they do not).

OK and you also service them on-site so if damage has been done, you send a technician to either fix it or replace it. And these service technicians work for your company or are they outside employees?

 

[plog]

I now remember (and reviewed) your prior post about this new owner and what you are giving him. If I were him, I'd want a working system, lumps and all. Not only are you trying to change tires on a moving car, you're trying to switch drivers while doing so. I'd give the new owner a working database so that it doesn't effect his take over of this. Then get his input and approval for fixing things.

So what database did you post? The old structure or the new proposed one?

 

[LarryE]

Sorry for the confusion.

We have an Access Database. The company is being sold, the new owner is only getting some of the data. I have to move specific data over to a new database for the new owner. In my creating the new vending database I'm trying to cleanup/fix some of the old tables I have. Many were created "on-the-fly" as I was learning Access and they are garbage and need to be cleaned up. Since we are starting with essentially a new database I thought now would be the perfect time to start normalizing and cleaning things up.

The service table is a large part of the database, and the new owner gets the data. Before I just moved everything over, I was trying to figure out if moving forward there would be a better way to store the data.

There is a better way. Normalized data in tables that are properly related to one another. I looked at the file you attached, and you have way too many tables. Many of them are actually fields that belong in tables and not tables themselves.

 

[wmix]

OK and you also service them on-site so if damage has been done, you send a technician to either fix it or replace it. And these service technicians work for your company or are they outside employees?

Yes, we service vending machines on site, all our employees.

 

[wmix]

I now remember (and reviewed) your prior post about this new owner and what you are giving him. If I were him, I'd want a working system, lumps and all. Not only are you trying to change tires on a moving car, you're trying to switch drivers while doing so. I'd give the new owner a working database so that it doesn't effect his take over of this. Then get his input and approval for fixing things.

So what database did you post? The old structure or the new proposed one?

Yes, while it would be easier to give him the working database, that's not part of the sale, he only gets some of the data as written into the purchase agreement.

What I posted was the start of the tables in a new database. I have to start moving data into those tables that he's allowed to have.

 

[LarryE]

Yes, while it would be easier to give him the working database, that's not part of the sale, he only gets some of the data as written into the purchase agreement.

What I posted was the start of the tables in a new database. I have to start moving data into those tables that he's allowed to have.

Are you going to input technicians hours spent on each service call and cost based on hourly rate?