Creating Packages of Services - diff discounts by Customer

[Marcotte]

I'm trying to put together a relatively simple Service Rate database. We sell Services, which can be combined into Packages. The Package price can be the sum of the individual Service prices, or it could be some discount off that total. For my purposes, I can assume that the discount will be manifested by a reduction in the price of one or more Services for that Package/Customer combo. The same package could be sold to multiple customers, but they could have different discounts. So far, this is what I have:

tblCustomerMaster
CustID (PK)
CustName

tblServiceMaster
ServiceID (PK)
ServiceDesc

tblPackageMaster
PackageID (PK)
PackageDesc

tblPackageDetail
PackageID (PK)
ServiceID (PK)
CustID (PK)
Discount



tblRates
ServiceID (PK)
CustID (PK)
Rate

The problem I'm running into is that when I try to create a form (and sub-form), I get too many records in my sub-form. I'd like to have a form that shows the Customer and Package in the main section, and in the sub-form, display the Services in that Package (for that Customer), the rate and discount, and then the calculated "net rate". However, I'm running into two problems.

First, I get too many "records" in my main form. I should only have one record for each Customer/Package combo as defined by the tblPackageDetail table (this may be due to how I've created the form).

Second, my subform (which is tabular) is showing multiple records for each service (one for each different customer rate). This occurs even though I've linked the subform to the main form based on ClientID. Perhaps I need to include the ClientID field from the Rates table as well.

[orange]

Have you tried defining each of these "entities" in a line or 2? It can often identify things that are different (quite similar but not exact) and may help with your situation.

Another thought is, suppose you look at your set up in general terms as a Customer --Order --OrderItems---Items and see if it "works" conceptually. Then refine as necessary. I'm not saying it will work, I'm just suggesting an option to get started without getting too deep into
various Packages and rates and Discounts for Different Customers.

Consider a Customer can make 1 or more Orders (if you want a "potential Customer" list then 0 or more Orders would apply)
An Order can involve/contain 1 or more Services
There is a list of all Services that are offered.
There is a current Rate for Each Service
You could have a Discount based on the number of Services on an Order
You could have a Discount based on the number of Orders associated with a Customer

In this sort of scenario, you could rename Order as Package.

If you have predefined sets of Services which you refer to as Packages, then it would seem you have
-predefined Packages and Services

So an Order could involve a predefined Package (actually the Services in such a Package) and /or individual Services.
If the Customer Orders a Package, then a Package Discount could apply, and possibly a Customer Discount based on the number of Orders that Customer has (a loyalty discount so to speak).

Just some thoughts to consider.

[Marcotte]

Have you tried defining each of these "entities" in a line or 2? It can often identify things that are different (quite similar but not exact) and may help with your situation.

Do you mean writing out a plain language description? I've done that in my head, and listed and drawn things out on paper, but perhaps a more formal definition process would help.

Another thought is, suppose you look at your set up in general terms as a Customer --Order --OrderItems---Items and see if it "works" conceptually.

At my last job I worked extensively with a large and complex ERP database. It had OrderMaster, OrderDetail, ItemMaster, CustomerMaster and Price tables which all interlinked in a very similar fashion to what I'm trying to do here. The key difference is that there is a many to many relationship between my Packages and Customers. The same package can be sold to two or more different customers. Or it could be sold to only one. And of course, the customer will (always) have multiple packages.

The easy way to do this would be to simply treat the packages as the base item. However, I'd like to be able to compare two packages that aren't exactly the same and extract the price build-up. This would help in pricing new packages for new customers. (Two Customers can have different prices for the same base Services, and by extension for the same Package.)

You could have a Discount based on the number of Services on an Order
You could have a Discount based on the number of Orders associated with a Customer

Discounts are all manually determined when the package is built for a specific Customer, so I don't need to worry about algorythmically calculating it. For example, delivery is included in almost every package for most customers, even though every customer has a delivery rate. It gets discounted for most of their packages, but would still be charged if they want a one-off delivery (i.e. with no other work), or a delivery to a second location.

Just some thoughts to consider.

Thanks. It helps to "talk" this out.

[orange]

I would try to remove the Master and Detail concept - at least not start with that.

When you have a many to many, you have to consider Junction Tables.

I'm going to give a list of free video tutorials that will help with the underlying concepts and database design.

Here are some free video tutorials that describe the processes of data modelling, normalization and entity relationship diagramming. There are other videos, but this group is by the same presenter and covers an example situation.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

The first few topics at this site are well worth the read.

http://www.rogersaccesslibrary.com/forum/topic238.html

Good luck with your project.

[orange]

You could consider a structure as per the attached jpg.

I was trying to see how a Customer could have a CustomPackage, or a standard Package or just a Service. The top of the diagram is an attempt
to have a number of CustomPackages for a Customer, and show that Packages are made up from 1 or more Services. And any CustomPackage has its own Discount. My understanding is that a Custom Package is made from a standard package and or Services.

Once Custom Packages and standard packages and Services have been defined/built, they become "Purchase-able items, so I clumped them inside a larger box to represent that an Item could be a Custom Package, a Standard package or a Service.

The lower half of the diagram I'm attempting to show that a Customer makes 1 or more Orders and an Order is composed of 1 or more OrderItems. And each OrderItem is an Item from the larger (Items) box mentioned above.

It's for consideration and discussion.

[Marcotte]

I would try to remove the Master and Detail concept - at least not start with that.

When you have a many to many, you have to consider Junction Tables.

That's what I was trying to do, with the PackageDetail acting as the Junction Table between Customers and Packages. I'll have to watch some of those videos and read the Roger's docs to get a better understanding of this though.

I was trying to see how a Customer could have a CustomPackage, or a standard Package or just a Service. The top of the diagram is an attempt
to have a number of CustomPackages for a Customer, and show that Packages are made up from 1 or more Services. And any CustomPackage has its own Discount. My understanding is that a Custom Package is made from a standard package and or Services.

Luckily I don't need to worry about tracking orders. The purpose of this is keep better track of our existing rate cards for clients, easily compare rates between clients and ease generation of new rate cards (either for new clients, or new rates for existing clients for services we do for other clients). Currently each client will have one or more rate cards stored either in Excel or Word. Sometimes, for older rate cards, we only have a PDF version. This makes looking up rates quickly difficult, and comparing across clients very tedious.

I'll try to describe a few examples which may make the nature of the data more clear. We do digital media services, such as:

• Digitize/Encode from tape (i.e. create a video file from a video tape
• Transcode file to file
• QC check of a file (there are several types of QC, including "spot" and "full")
• Delivery of a file
• Add subtitles to a file

Each Customer may have different rates for each of these services, and may not have a rate for some of them (e.g. subtitles). So, for example:

Customer A:
Encode $100
Transcode $75
QC Check $50
Delivery $25
Subtitles $75

Customer B
Encode $125
Transcode $75
QC Check no rate defined
Delivery $30
Subtitles no rate defined

Package 1 - Encode, QC and Delivery
Customer A Pays $125 (QC is included, but they pay for delivery)
Customer B Pays $125 (QC and delivery are included)

Package 2 - Encode to one format, Transcode to 2nd format, QC 2 files, deliver 2 files
Customer A Pays $225 (Encode+Trascode+Delivery+Delivery; QC is included)
Customer B pays $200 (Encode+Trascode; delivery and QC included)

Anyway, other fires are igniting, so I'll likely have to table this project for a week or so. Maybe stepping away for a bit and coming back to it fresh will give me some perspective and/or ideas.

[orange]

My first reaction based on your description is that you don't have "standard packages".
For example Package 1 both Customers pay $125 but CustA pays for Delivery, and CustB doesn't.
Can you explain how the Total cost is the same, the Services involved are the same, but 1 pays for Delivery?

I have revised a model based on what was written and discussed. I don't have any Discount figures. I did add a ServiceQuantity field to the Package because you have 2 QC and 2 Deliveries in Package2.

You will have to identify exactly what a Service and ServiceRate is consistently, if you are going to use database effectively.

The other option is to have a specific price for a Package for a Customer which may be what you are doing. But I don't see how you can use Discount to rationalize/reconcile the different prices of "services".

If you start with specific services and each service having a specific base rate, then include Services in Packages, then assign Customers to Packages as CustomPackages with specific costs, you can accommodate the Cost of a Package to a Customer; and that Package cost can be different for each/some Customers. However, if you arbitrarily assign services and rates, and service quantities to a Package, and you try to use the Service Rate* Quantity - Discount to reflect PackageCost or Customer Charges, you have a problem with the use of ServiceRate.

I have attached a query design and the result showing the Details for Customer1 (CustA) and Package2.

It appears your Service Rates may be Customer dependent, but I don't know your business.

[Marcotte]

My first reaction based on your description is that you don't have "standard packages".
For example Package 1 both Customers pay $125 but CustA pays for Delivery, and CustB doesn't.
Can you explain how the Total cost is the same, the Services involved are the same, but 1 pays for Delivery?

It's just because every deal with a client is done slightly differently. In reality there is more conformity (e.g. delivery is almost always included when there is other services ordered), but I was trying to describe the edge cases.

I did add a ServiceQuantity field to the Package because you have 2 QC and 2 Deliveries in Package2.

Good point.

The other option is to have a specific price for a Package for a Customer which may be what you are doing. But I don't see how you can use Discount to rationalize/reconcile the different prices of "services".

It may be that I'm trying to do too much, or be too "cute." Perhaps I should just simply define the "Service" as what we will actually bill for (i.e. the Package) and not worry about having that extra level of granularity. On the other hand, I have to believe that there are systems out there that handle kits or packages, so the problem should be solvable.

If you start with specific services and each service having a specific base rate, then include Services in Packages, then assign Customers to Packages as CustomPackages with specific costs, you can accommodate the Cost of a Package to a Customer;

That sounds like the way to go probably.

and that Package cost can be different for each/some Customers. However, if you arbitrarily assign services and rates, and service quantities to a Package, and you try to use the Service Rate* Quantity - Discount to reflect PackageCost or Customer Charges, you have a problem with the use of ServiceRate.

Yeah, that would be problematic.

I have attached a query design and the result showing the Details for Customer1 (CustA) and Package2.

It appears your Service Rates may be Customer dependent, but I don't know your business.

Thanks, I'll take a look. Yes, service rates are very customer dependent, due to market power of client, volume commitments, effectiveness of sales person, etc.

Thanks for your help and feedback. It's much appreciated. I probably won't get a chance to work on this for the next week and a half, but when I do I'll post back to let you know my results or lack thereof.

[orange]

No problem. Just be sure to define what you really have or want - don't opt for some sort of compromise that can be worked out later.
Design your database; implement the parts you need; but keep the big picture in view.