Relationships for standard & special rates (1 Viewer)

[Sketchin]

I am building a database for generating quotes for a service shop and am struggling with how to handle our flat rates and special rates for customers. There is a fixed list of tasks that are considered a flat rate, stuff like Visual Inspection, Pressure Test, Steam, Sensor Inspection... and so on.

We give discounts to larger customers, which may or may not be a simple "All FlatRates *.25 = Discount" calculation, so I need to be able to override the default rate for each item if required.

These are my tables:

• tblCompanies: This table will contain details about each company (e.g., CompanyID, CompanyName).
• tblFlatRates: This table holds the standard or default rates for each service (e.g., FlatRateID, Description, DefaultRate). The FlatRateID is the primary key.
• tblCompanyFlatRates: This table holds CompanyID from tblCompanies and FlatRateID from tblFlatRates. It will also have a field for the special rate.
• tbljncQuoteFlatRate: This table allows the user to add one or more flat rate tasks to the Quote. QuoteFlatRateID is the primary key with QuoteID and FlatRateID as FKs.
• tblQuotes: This is the main table where quotes are stored for each work order. QuoteID is the primary key

Relationships:

• tblCompanies to tblCompanyFlatRates (one-to-many relationship on CompanyID). One company can have many special rates.
• tblFlatRates to tblCompanyFlatRates (one-to-many relationship on FlatRateID). One service can have many special rates (one for each Flat Rate task).
• tblFlatRates to tbljncQuoteFlatRate (one-to-many relationship on FlatRateID). One Quote can have many flat rates (one for each Flat Rate task).
• tblQuotes to tbljncQuoteFlatRate (one-to-many relationship on QuoteID). One Quote can have many flat rates (one for each Flat Rate task).

I have a main form with tblQuotes as a record source and a subform with tblFlatRates as the record source filtered to the chosen company on the main form. This doesn't really work as is, so I need to redesign the subform so when a company is chosen, either the default rate is populated, or if there is one, the special rate is populated.

Questions:

1. Do the tables and relationships make sense for this application?
2. If the tables and relationships make sense...how could I structure a form so all entries from tblFlatRates would be listed and all the fields from tblCompanyFlatRates would be shown as well so you could input a special rate for each flat rate?

I am just having a heck of a time conceptualizing this setup for some reason!

 

[MajP]

I am going to modify the names here to make it more conceptually clear. But you can use your names. In my mind the entity is a Service item which has a description and a flat rate. IMO a standard flat rate for a service is property not the entity.


tblServiceStandardRates
--ServiceID
--ServiceDescription
--StandardRate

tblCompanySpecialRates
--Company_ServiceSpecialRateID
--ServiceID_FK
--SpecialRate

In your junction table I would be storing the actual Rate and not the keys to these tables. This allows you to have a single field, historical, and editable if needed for one offs (maybe there is a negotiated rate specific for that invoice, or a monthly special). The rate will be historical based on when the order was done. If your rates go up in the future you do not want the rate on the historical invoices to change. They would if they hold a key.

tblJuncQuoteFlatRate
--QuoteID_FK
--ServiceID_FK (to tblServiceStandardRate but only really using the info on the service)
--AppliedRate (put in the actual value pulled from one of the tables, edit it for any one-offs)
--RateType (Standard, Company Special, Edited)

Then in your subform you can build the interface so that you pull down a serviceID and it then also populates the AppliedRate and mark as a standard rate or a special rate in RateType. Your pull down can be based on a union query that shows the rates and if it has a special rate only show that rate.

 

[MarkK]

What is a "Task?" This object figures prominently in your prose, but not in your schema. Is a quote comprised of many tasks? Is each task, as a member of a quote, subject to different discounts?

 

[Sketchin]

What is a "Task?" This object figures prominently in your prose, but not in your schema. Is a quote comprised of many tasks? Is each task, as a member of a quote, subject to different discounts?

The workflow will begin with the user creating a new quote and adding a list of parts and labor items to it. From there, they will select the specific tasks that were performed—such as changing brakes or replacing suspension—and enter the number of hours required for each task. Parts can then be assigned to the relevant tasks as needed. After that, any applicable flat-rate jobs can be added to the quote as well.


The purpose of structuring the flat-rate jobs this way is to allow us to track individual activities in detail. For example, some flat rates include inspections such as pressure tests, internal inspections, external inspections, and leak tests. For compliance, I need a record of which employee performed each inspection, along with the date it was completed. Later, I’ll use this data to generate reports for government auditors. I’m not concerned with implementing the “who performed it and when” portion right now—once the table structure is set up correctly, that piece will naturally fall into place.

The parts & labour pieces are set up and working fine, I just need to figure out how to implement default rates and special rates.

 

[Sketchin]

I am going to modify the names here to make it more conceptually clear. But you can use your names. In my mind the entity is a Service item which has a description and a flat rate. IMO a standard flat rate for a service is property not the entity.


tblServiceStandardRates
--ServiceID
--ServiceDescription
--StandardRate

tblCompanySpecialRates
--Company_ServiceSpecialRateID
--ServiceID_FK
--SpecialRate

In your junction table I would be storing the actual Rate and not the keys to these tables. This allows you to have a single field, historical, and editable if needed for one offs (maybe there is a negotiated rate specific for that invoice, or a monthly special). The rate will be historical based on when the order was done. If your rates go up in the future you do not want the rate on the historical invoices to change. They would if they hold a key.

tblJuncQuoteFlatRate
--QuoteID_FK
--ServiceID_FK (to tblServiceStandardRate but only really using the info on the service)
--AppliedRate (put in the actual value pulled from one of the tables, edit it for any one-offs)
--RateType (Standard, Company Special, Edited)

Then in your subform you can build the interface so that you pull down a serviceID and it then also populates the AppliedRate and mark as a standard rate or a special rate in RateType. Your pull down can be based on a union query that shows the rates and if it has a special rate only show that rate.

Thanks MajP, I think I follow the logic here and believe it might work. I will follow up if I need any clarity. Thanks for taking the time to help me out!

 

[LarryE]

I am building a database for generating quotes for a service shop and am struggling with how to handle our flat rates and special rates for customers. There is a fixed list of tasks that are considered a flat rate, stuff like Visual Inspection, Pressure Test, Steam, Sensor Inspection... and so on.

We give discounts to larger customers, which may or may not be a simple "All FlatRates *.25 = Discount" calculation, so I need to be able to override the default rate for each item if required.

These are my tables:

• tblCompanies: This table will contain details about each company (e.g., CompanyID, CompanyName).
• tblFlatRates: This table holds the standard or default rates for each service (e.g., FlatRateID, Description, DefaultRate). The FlatRateID is the primary key.
• tblCompanyFlatRates: This table holds CompanyID from tblCompanies and FlatRateID from tblFlatRates. It will also have a field for the special rate.
• tbljncQuoteFlatRate: This table allows the user to add one or more flat rate tasks to the Quote. QuoteFlatRateID is the primary key with QuoteID and FlatRateID as FKs.
• tblQuotes: This is the main table where quotes are stored for each work order. QuoteID is the primary key

Relationships:

• tblCompanies to tblCompanyFlatRates (one-to-many relationship on CompanyID). One company can have many special rates.
• tblFlatRates to tblCompanyFlatRates (one-to-many relationship on FlatRateID). One service can have many special rates (one for each Flat Rate task).
• tblFlatRates to tbljncQuoteFlatRate (one-to-many relationship on FlatRateID). One Quote can have many flat rates (one for each Flat Rate task).
• tblQuotes to tbljncQuoteFlatRate (one-to-many relationship on QuoteID). One Quote can have many flat rates (one for each Flat Rate task).

I have a main form with tblQuotes as a record source and a subform with tblFlatRates as the record source filtered to the chosen company on the main form. This doesn't really work as is, so I need to redesign the subform so when a company is chosen, either the default rate is populated, or if there is one, the special rate is populated.

Questions:

1. Do the tables and relationships make sense for this application?
2. If the tables and relationships make sense...how could I structure a form so all entries from tblFlatRates would be listed and all the fields from tblCompanyFlatRates would be shown as well so you could input a special rate for each flat rate?

I am just having a heck of a time conceptualizing this setup for some reason!

You could try a design like this:

so that:

1. Each Company could have multiple QuoteDates over time
2. Each QuoteDate could have multiple quotes
3. Each Quote has a standard FlatRate and a SpecialRate (if any) and a QuoteDiscount applied (if any). Although, not shown, you could keep Discount Rates in the TblCompany if that is appropriate in your case.
4. Each Service could have multiple FlatRates over time. If you wish, you could add a FlatRateDate to the TblServiceRate so you can track FlateRates over time with a date implemented.

On the Quote Form, you can then indicate the normal FlatRate, any SpecialRate and any QuoteDiscount percentage to be applied against the quote total. You can also compare flate rates to special rates on the form and calculate the difference (if any). Or you could make a separate quote for flat rate and a separate one for special rates (with or without discounts) as you wish.

 

[MajP]

@LarryE,
I think what @Sketchin is saying there is a Company specific list of special rates.

So if Pressure Testing has a flat rate of 100 (for most people)
Company A has an agreement that they will always be charged 75
Company B may always be charged 80.

So you need that table TblCompanies_SpecialRates

The way you have it is a Quote Specific Rate, which I think still could happen, but you are missing that table.
The quote rate can be pulled from the standard rate, or the company special rate, or if you are running a special you can edit for that specific quote.

 

[LarryE]

@LarryE,
I think what @Sketchin is saying there is a Company specific list of special rates.

So if Pressure Testing has a flat rate of 100 (for most people)
Company A has an agreement that they will always be charged 75
Company B may always be charged 80.

So you need that table TblCompanies_SpecialRates

The way you have it is a Quote Specific Rate, which I think still could happen, but you are missing that table.
The quote rate can be pulled from the standard rate, or the company special rate, or if you are running a special you can edit for that specific quote.

OK then can't we move the SpecialRate field to the ServiceRate table and connect the Company and Service Rate tables so each Service Rate has a Service FK and a Company FK. Then you can still enter a company-specific special rate, if necessary, without the extra table? I guess I am attempting to use the KISS method of design...

 

[MajP]

Then you can still enter a company-specific special rate, if necessary, without the extra table?

That would make sense as long as reference table of standard rates. If not that would create a painful data entry.
Assume you have 100 standard rates and 100 companies.
TblServiceRates requires 10,000 entries. Every company and every flat rate and special rate. But if you are just making a few edits to some special cases then you do it as follows.

1. Add a new company which causes an insert from tblStandardRates into tblCompanyServiceRates. All the standard rates
TblCompanyRates
--CompanyID_FK
--ServiceID_FK
--ServiceRate
--RateType (Standard, Special) (no need for two fields for the rate since company specific)

2. Edit any services for that company with any special rates which marks the rate as Special

Now the Quote table would be the same.

 

[LarryE]

That would make sense as long as reference table of standard rates. If not that would create a painful data entry.
Assume you have 100 standard rates and 100 companies.
TblServiceRates requires 10,000 entries. Every company and every flat rate and special rate. But if you are just making a few edits to some special cases then you do it as follows.

1. Add a new company which causes an insert from tblStandardRates into tblCompanyServiceRates. All the standard rates
TblCompanyRates
--CompanyID_FK
--ServiceID_FK
--ServiceRate
--RateType (Standard, Special) (no need for two fields for the rate since company specific)

2. Edit any services for that company with any special rates which marks the rate as Special

Now the Quote table would be the same.

OK. Got your good stuff....