However this solution at present, and as I understand it, only works with "one" record (combo) at a time.
#Dbschema cost how to#
There have been some ideas on how to do this, Neil McGuilgan asked a great question leading towards a possible solution using Relational Division (/questions/45829/what-is-the-name-of-this-type-of-query-and-what-is-an-efficient-example) and this book (helped a lot. In fact, I have done so with more flexibility than I thought I could do in the first deployment of the system.Īll though there have been advances with Problem 2, it is not solved to satisfaction. So, with the research and support provided by the community up until know, I have been able to solve Problem #1. ProductFeatureFrom Product ProductFeatureTo ProductFeature product ProductFeatureApplicabilityTypeĬombo Beverage Cheeseburger Combo RequiredĬombo Side Bacon Hamburger Combo RequiredĬombo Beverage Bacon Hamburger Combo RequiredĬombo Side Bacon Cheeseburger Combo RequiredĬombo Beverage Bacon Cheeseburger Combo Required Product productFeature ProductFeatureApplicabilityTypeīacon Hamburger Combo Combo Side Requiredīacon Hamburger Combo Juice Flavor Requiredīacon Cheeseburger Combo Combo Side Requiredīacon Cheeseburger Combo Juice Flavor Required PRODUCTīacon Cheeseburger Combo Bacon Cheeseburger
.png "dbschema cost")
Here is a link to a flow chart giving an example of a combo, a fast and visual way to understand the table structure. I'm also including sample data from the entities and attributes that affect this question (to simplify reading the data, I'm putting in name/descriptions in place of Foreign Keys): inventory is not at play here, so no inventory entities are present).
#Dbschema cost update#
To better describe the problems at hand and to update the work done thus far to solve them I'm incluiding an ERD of the Product Model limited to the entities and attributes that affect the issue (i.e. This must work with multiple promotions). maybe what they ordered fulfills requirements for promotion 1 and 3, but promotion 3 is less expensive. Another promotion might depend on when your last purchase occured or if you have purchase more than $X in Y timeframe.ġ) How do I structure the tables so I my create the different packages or promotions in a way that is very flexible to add different types of promotions with different requirements?Ģ) When they order like Case B (or a mix of Case A and Case B) how do I structure my query so that I can test to see what product mix(es) are in the order, and update the prices/descriptions accordingly? Ultimately, the best result for this query would return which packages and promotions have requirements fulfilled in order of which gives the most benefit to the customer (i.e. Other promotions are dependent on quantity, so if you buy 2 of B you get 20% off and/or dependent upon time, it is only valid after 5 PM or before 10% off if before 10am. According to the rules set forth by "Combo" Package, only 2 combos would apply because S is not a combo item. Then they might add that they want 1 of product B with 1 R and 1 Z.Ĭase B: Sometimes customers will add 1 A and 2 B, 2 Q, 1 S, 2 X and 1 Z. They wan't one of Product A, and they add Product Q and Product P to that to create a package that has a discounted price. The company has Packages, for example Package "Combo": for product A or B, if you choose 1 of Q or R and 1 of X, Y or Z you get a $20 discount.Ĭase A: Sometimes customers add on to a base product when placing an order, for example: To explain my problem, I will use the following info: Product Category Price I'm working on a new point of sale for a company that products for different prices depending on the product mix.
0 kommentar(er)
