I don't think you have a clear enough mental picture of what your
different pricing structures are. You describe:
a) fundamental unit price
b) price breaks due to volume discounts
c) price breaks due to coupons
d) price increases based on options. Options include: embroidered logo,
screened logo, total colors =2, total colors = 3, etc
e) price breaks due to "a sale" being in effect.
I cannot imagine that you have the ability to define EVERY combination of
base price + volume discount + coupon + options ahead of time. So long as
you can tally them up when it becomes time to construct the "unit price"
during the checkout phase, you should have the information you need.
A "sale price" can either be a fixed deduction (across all volume levels)
, a percentage deduction( across all volume levels), a fixed deduction for
certain volumes, or a percentage deduction for certain volumes. Nothing
says you can't have two sales going on for the same item at the same time
(10% additional off on all sales < 200 units, 20% off on all sales >= 200
units)
Coupons (special offers, one-time discounts, Sales Rep credits, etc.) can
apply to one or more products, depending on how they are defined.
Typically, a user will need to add the coupon to their "cart" during
checkout in order for it to count towards the purchase. While considering
whether to allow the coupon into the cart of not, your application will
need to decide:
a) is the coupon valid (in date, applies to 1+ products in this purchase)
b) which products it is valid for
c) how much discount and for how many product units should the discount
apply.
It's basically a bookkeeping thing but you need enough information in the
"coupon" table to be able to make these decisions.
What I am saying is start simple, like with your volume pricing table.
Make sure you can put into and get out of it all of the information you
will need to determine the correct "unit price" for the sales ticket.
Then, start with your add-ons and product options (colors, logos, etc.)
Some product-productvolume-addon-addonvolume prices will be nothing ("if
you buy > 200 shirts, embroidered logos are free"). You will need a table
that can store all of the definitions for the price breaks. That doesn't
mean that each kind of product-feature-volume-price break cannot apply to
more than one product. In the example I listed, you may have a generic
rule that all shirts (of which you could have 50 styles) may qualify for
the free logo upgrade if bought in lots of 200 or more. What you would
have is a many-to-many relationship between your option costs and your
products.
If I have confused you, I didn't mean to. I just want you to sit down,
with pencil and paper (or use a whiteboard or any means you are
comfortable with) and determine how many bits of information you would
need if you didn't have a computer to help you out. If all you had was
lists of things on paper, what lists of what things would you need (as a
person) to completely fill out an order and bill it correctly to the user?
By putting yourself in the role of your program an modelling out what
information you must have to make certain decisions, you will make many
insights as to what your database design needs to be. The longer you wait
to do this analysis, the harder it is going to be to fix your design.
What you have to remember is that "sales" people do nothing but think up
new ways of screwing us up. You as the DBA have to allow yourself the
freedom to define almost any combination of product+price+discount+premium
so that no matter what they think of, we can make it fit in the database.
It's the application's responsibility to assemble the correct price based
on available information and if that information is not available (re: in
the database) people will complain. An online catalog is one of the most
complex data structures just because of the flexibility it needs to have.
In response to request to review your table structures:
extended_prices:
a) I would include the lower-bound of a pricing level as well, that way
you don't have to find the max-of-group just to know which pricing tier
you are in.
b) I would also allow for some way to define a "default" set of prices.
How would you define a set of default prices with this table the way it is
(small begindate and huge enddate values?)
ac_categories:
a) consider using pre-ordered tree traversal as another way to define your
categories heirarchies
(http://www.sitepoint.com/article/hierarchical-data-database/2)
ac_products:
a) colors: probably needs to be on a separate table and linked with the
product.
CREATE TABLE ac_products_colors (
ac_products_id INT NOT NULL,
ac_colors_id INT NOT NULL,
PRIMARY KEY (ac_product_id, ac_colors_id)
);
This gets trickier if you allow only certain color combinations.
b) The optimizer will not use an index if it thinks that more than 30% of
the rows match the value(s) it is looking for. That means that the index
on STATUS is probably going to be ignored until at least 70% of this table
has a status of 0. Basically, it's going to take up room and do you no
good.
ac_products_has_ac_categories:
a) that's not how to define a foreign key. First all 3 tables involved
must be InnodB and unless that's your default table type, they won't be.
Second, you have to declare them as FKs not INDEXes. Third, the PK will
already act as an index for the column ac_products_product_id so you don't
need a separate index for that column.
CREATE TABLE ac_products_has_ac_categories (
ac_products_product_id INTEGER(8) UNSIGNED NOT NULL,
ac_categories_cat_id INT(6) NOT NULL,
PRIMARY KEY(ac_products_product_id, ac_categories_cat_id),
INDEX ac_products_has_ac_categories_FKIndex2(ac_categories_cat_id),
FOREIGN KEY (ac_products_product_id) REFERENCES ac_products(product_id),
FOREIGN KEY (ac_categories_cat_id) REFERENCES ac_categories(cat_id)
) ENGINE=InnoDB;
ac_extended_prices:
a) what's the difference between ac_products_product_id and product_id
for this table?
b) what's the difference between ac_pricemodtypes_pricemodtype_id and
pricemodtype_id?
c) same thing for price_per and price_per_mod?
Please, do not be discouraged. I am honestly trying to help you think this
through. As I mentioned before, this can be a very complicated data
structure depending on how many different types of "things" you need to
support. Decide on what "things" you need to have in your system and what
you need to know about each "thing". Figure out how each "thing" relates
to other "things". Then you have enough information to work out some
preliminary storage strategies. Walk through some "what-if" scenarios and
see what your preliminary structures will and will not support. Make
refinements and test more "what-ifs". Keep this up until your data
structures meets your business needs then you are ready to go. Make
sense?
Here to help,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote on 10/19/2005 02:14:52 PM:
> Thanks guys for really detailed answers.
>
> After your emails I talked to project supervisor and found that there is
> "some changes" in the project:
> (i) do you know in advance all the kinds of price extensions that can
> come up?
> - I hope I know them now :(
>
> (ii) do you want the price rules to be (a) in the database or (b) in the
> app?
> (iii) if the answer to (ii) is (a), do you want the rules in stored
> procedures, or in tables which application code must parse?
> - Those two I really didn't get. If you thought on this: there is no
> rule in making prices for different number of items in pack. next price
> is NOT for x% lower or for $x lower. There is no rule. If you were
> thinking on something else please explain. Thanks.
>
> (iv) does the app need to track price history (e.g. so it can recreate a
> price computation from six months ago)?
> - This would be actually more online catalog where visitor/customer will
> create an inquiry. And we don't need to track a purchase history in this
> case.
>
> But, Peter's 2nd part is actually "touching" the change in the project:
> product can have more then 2 prices. E.g. if you select shirt with your
> logo embroidered - it's one price. If your logo will be screened on the
> shirt - other price. And then if the shirt is on sale - 2 more prices
> Total 4 different prices have to be shown on catalog.
> The solution:
>
> CREATE TABLE pricemodtypes (
> pricemodtype_id INT AUTO_INCREMENT PRIMARY KEY,
> name CHAR(20)
> )
>
> CREATE TABLE extended_prices (
> epid INT AUTO_INCREMENT PRIMARY KEY,
> product_id INT NOT NULL,
> pricemodtype_id INT NOT NULL,
> qty_up_to SMALLINT NOT NULL,
> begindate DATE NOT NULL,
> enddate DATE NOT NULL,
> price_per DECIMAL(10,2) NOT NULL,
> price_per_mod DECIMAL(10,2) NULL
> );
>
> will be fine?
>
> Actually, there is what I have for the moment for my DB:
>
> categories and subcategories:
> CREATE TABLE ac_categories (
> cat_id INT(6) NOT NULL AUTO_INCREMENT,
> cat_name VARCHAR(45) NULL,
> cat_description TEXT NULL,
> cat_parent INTEGER(4) UNSIGNED NULL,
> cat_status ENUM('0','1') NULL DEFAULT 0,
> PRIMARY KEY(cat_id),
> INDEX ac_categories_index1(cat_status)
> );
>
> CREATE TABLE ac_products (
> product_id INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT,
> product_no VARCHAR(12) NULL,
> product_name VARCHAR(45) NULL,
> product_description TEXT NULL,
> product_colors TEXT NULL, // since there is hundreds of different
> colors and color combination, we will have colors as description
> product_includes TEXT NULL, // shows what is includes in price (e.g
> how many colors for logo and how much costs additional color)
> product_catalog VARCHAR(45) NULL, // products are in groups of
> catalogs - for internal use
> product_status ENUM('0','1') NULL, // is product available (visible at
> front end)
> product_supplier VARCHAR(45) NULL,
> product_start_date DATE NULL,
> product_exp_date DATE NULL,
> PRIMARY KEY(product_id),
> INDEX ac_products_index1(product_start_date, product_exp_date),
> INDEX ac_products_index2(product_status),
> );
>
>
> since, one product can be in more than one category:
> CREATE TABLE ac_products_has_ac_categories (
> ac_products_product_id INTEGER(8) UNSIGNED NOT NULL,
> ac_categories_cat_id INT(6) NOT NULL,
> PRIMARY KEY(ac_products_product_id, ac_categories_cat_id),
> INDEX ac_products_has_ac_categories_FKIndex1(ac_products_product_id),
> INDEX ac_products_has_ac_categories_FKIndex2(ac_categories_cat_id)
> );
>
> CREATE TABLE ac_extended_prices (
> epid INTEGER(8) UNSIGNED NOT NULL AUTO_INCREMENT,
> ac_pricemodtypes_pricemodtype_id INT(8) NOT NULL,
> ac_products_product_id INTEGER(8) UNSIGNED NOT NULL,
> product_id INTEGER(8) UNSIGNED NULL,
> pricemodtype_id INTEGER(8) UNSIGNED NULL,
> qty_up_to INTEGER(8) UNSIGNED NULL,
> begindate DATE NULL,
> enddate DATE NULL,
> price_per DECIMAL(10,2) NOT NULL,
> price_per_mod DECIMAL(10,2) NULL,
> PRIMARY KEY(epid),
> INDEX ac_extended_prices_index_date(begindate, enddate),
> INDEX ac_extended_prices_index_qty(qty_up_to),
> INDEX ac_extended_prices_FKIndex1(ac_products_product_id),
> INDEX ac_extended_prices_FKIndex2(ac_pricemodtypes_pricemodtype_id)
> );
>
> CREATE TABLE ac_pricemodtypes (
> pricemodtype_id INT(8) NOT NULL AUTO_INCREMENT,
> name CHAR(40) NULL,
> PRIMARY KEY(pricemodtype_id)
> );
>
>
>
> Your opinion?
>
> Thanks for help.
>
> -afan
>
>
>
> Peter Brawley wrote:
>
> > afan,
> >
> > >For the same project (below) I have problem with building table for
> > product prices.
> > >In "regular" online store, price is usually part of the products
table.
> > >But, I need a solution for multiple prices. E.g.
> > >QTY - 25 50 100 200
> > >Price - $1.59 $1.39 $1.19 $0.99
> >
> > >Also, if product is On Sale I need to be shown both prices: regular
> > and sale price
> > >QTY - 25 50 100 200
> > >Price - $1.59 $1.39 $1.19 $0.99
> > >Sale - $0.99 $0.99 $0.99 $0.99
> >
> > First two footnotes to the excellent advice offered by Rhino & Shawn
> > on your categories, products & products_categories tables:
> >
> > 1. It will be best to type the primary & foreign keys identically--all
> > unsigned, or all not.
> >
> > 2. To avoid rounding errors, use DECIMAL rather than FLOAT for money
> > columns.
> >
> > Before you model extended price computations, you have to ask & answer
> > crucial questions:
> > (i) do you know in advance all the kinds of price extensions that can
> > come up?
> > (ii) do you want the price rules to be (a) in the database or (b) in
> > the app?
> > (iii) if the answer to (ii) is (a), do you want the rules in stored
> > procedures, or in tables which application code must parse?
> > (iv) does the app need to track price history (eg so it can recreate a
> > price computation from six months ago)?
> >
> > Suppose the answers are those that most conventionally apply: only qty
> > and sale will ever come up, the rules will be in the app, and you can
> > leave history to the backups. Then you can take a very simple,
> > semi-normalised approach (leaving out some details):
> >
> > CREATE TABLE extended_prices (
> > epid INT AUTO_INCREMENT PRIMARY KEY,
> > product_id INT NOT NULL,
> > qty_up_to SMALLINT NOT NULL,
> > price_per DECIMAL(10,2) NOT NULL,
> > price_per_sale DECIMAL(10,2) NULL
> > );
> >
> > which permits you to enter whatever (qty cutoffs, price, sale) combos
> > are desired for any desired products, and find them for any product
> > with a very simple query. There is a risk, though: in six months the
> > client may find that new price extensions are needed, and/or that she
> > needs history after all.
> >
> > Now, add the wrinkles that other possible, but presently unidentified
> > price extensions (eg 'special promotions', 'coupons', &c) will be
> > required, and that history must be tracked. Now you need at least,
> > again normalising only partly...
> >
> > CREATE TABLE pricemodtypes (
> > pricemodtype_id INT AUTO_INCREMENT PRIMARY KEY,
> > name CHAR(20)
> > )
> >
> > CREATE TABLE extended_prices (
> > epid INT AUTO_INCREMENT PRIMARY KEY,
> > product_id INT NOT NULL,
> > pricemodtype_id INT NOT NULL,
> > qty_up_to SMALLINT NOT NULL,
> > begindate DATE NOT NULL,
> > enddate DATE NOT NULL,
> > price_per DECIMAL(10,2) NOT NULL,
> > price_per_mod DECIMAL(10,2) NULL
> > );
> >
> > the query to retrieve all the prices for a product is more complicated
> > but still straightforward.
> >
> > Of course wrinkles multiply as if conjured by a Sorcerer's Apprentice.
> > Perhaps we should pause here for a breath. Is this the info you need?
> >
> > PB
> >
> >
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
