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
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
