How is the design for this complex requirement ?

31 Aug 2006 3:47 PM

krislioe

Hi All,

We have a Discount scheme that can be given for all possible
combination of all
customer, area and product hierarchy (attached below).

for example :

Promotion No.001 =>for sales on : CustomerGroup 'A', Area 'B', and
ProductBrand 'C' will get discount 2 %
Promotion No.002 =>for sales on : AllCustomer, District 'D', and
Product 'E' will get discount 2.5 %
Promotion No.003 =>for sales on : CustomerType 'F', AllArea, and
ProductGroup 'G' will get discount 3 %
.... and any possible combination

And it is possible for an Order / Invoice to get discount from two or
more different promotion.
e.g: Order 001, get 2 % from Promotion No.001 and 3 % from Promotion
No.003, so totally it gets 5% discount.

(1) Customer Hierarchy
AllCustomer
CustomerGroup
CustomerType
Customer

(2) Area Hierarchy
AllArea
Region
District
Area

(3) Product Hierarchy
AllProduct
ProductGroup
ProductBrand
Product

Our Orders Data has following attributes :
OrderNo.
Customer
Area
Product
Gross
Discount

The question is : I am looking for the efficient way to do this,

1) How should I design the tables to accomodate all possible discount
combination above ?
2) How is the query to determine the total discount that an Order
should get ?

Thank you for your help,
xtanto

1 Sep 2006 10:55 PM

Hugo Kornelis

On 31 Aug 2006 08:47:54 -0700, krisl***@gmail.com wrote:

>Hi All,
>
>We have a Discount scheme that can be given for all possible
>combination of all
>customer, area and product hierarchy (attached below).
(snip)
>The question is : I am looking for the efficient way to do this,
>
>1) How should I design the tables to accomodate all possible discount
>combination above ?
>2) How is the query to determine the total discount that an Order
>should get ?

Hi xtanto,

You don't mention the CustGroups, CustTypes, Customers, Regions,
Districts, Areas, ProductGroups, ProductBrands, and Products tables.
I'll assume that you have all these, and that they are correctly
normalized (so that CustTypes has a foreign key into CustGroups,
Customers has a foreign key into CustTypes, Districts has a foreign key
into Areas, etc.)

I'll give you two solutions for this. Both share the same design for the
discounts table:
CREATE TABLE Promotions
              (PromotionNo int NOT NULL,
               PromotionName varchar(80) NOT NULL,
               Discount numeric(5,3) NOT NULL,
               CustGroup int NULL,
               CustType int NULL,
               Customer int NULL,
               Region int NULL,
               District int NULL,
               Area int NULL,
               ProductGroup int NULL,
               ProductBrand int NULL,
               Product int NULL,
               PRIMARY KEY (PromotionNo));
You'll probably want to add foreign key constraints into the CustGroups,
CustTypes, Customers, Regions, Districts, Areas, ProductGroups,
ProductBrands, and Products tables. You'll also want to add check
constraint to insure that at most one out of each triplet of criteria is
not null. (They can all be NULL for all customers, all areas, and all
products).

In the first solution, you'd have a second table to hold the discounts
for each possible combination of customer, area, and product. Something
like this:
CREATE TABLE PromotionsHelper
              (PromotionNo int NOT NULL,
               PromotionName varchar(80) NOT NULL,
               Discount numeric(5,3) NOT NULL,
               Customer int NOT NULL,
               Area int NOT NULL,
               Product int NOT NULL,
               PRIMARY KEY (Customer, Area, Product, PromotionNo));

To keep this table current, use either of these methods, or a mixture:
1. Use triggers to change PromotionsHelper when any of the underlying
tables change. Remember that you need triggers on Promotions, but also
on the CustGroups, CustTypes, Customers, Regions, Districts, Areas,
ProductGroups, ProductBrands, and Products tables. Note that these
triggers can slow down operations on those tables.
2. Schedule a job to periodically rebuild the PromotionHelper table from
scratch and accept stale data in between runs of that job.
3. (SQL Server 2005 only). Use Service Broker to asynchronously kick off
a job to change PromotionsHelper from triggers on the underlying tables.

Note that this table can grow very large if you have lots of customers,
products, and areas. However, the query to find total discount for an
order gets extremely simple (and fast, due to the PRIMARY KEY index):
SELECT     o.OrderNo, SUM(p.Discount)
FROM       Orders           AS o
INNER JOIN PromotionsHelper AS p
        ON   p.Customer        = o.Customer
        AND p.Area            = o.Area
        AND p.Product         = o.Product
GROUP BY   o.OrderNo;

The second solution dooesn't use an extra table. This means that the
query to find total discount gets way more complicated. Something like
this:

SELECT     o.OrderNo, SUM(p.Discount)
FROM       Promotions       AS p
LEFT JOIN CustTypes        AS ct
        ON   ct.CustGroup      = p.CustGroup
        OR   ct.CustType       = p.CustType
INNER JOIN Customers        AS c
        ON   c.CustType        = ct.CustType
        OR   c.Customer        = p.Customer
        OR   COALESCE (p.Customer, p.CustType, p.CustGroup) IS NULL
LEFT JOIN Districts        AS d
        ON   d.Region          = p.Region
        OR   d.District        = p.District
INNER JOIN Areas            AS a
        ON   a.District        = d.District
        OR   a.Area            = p.Area
        OR   COALESCE (p.Region, p.District, p.Area) IS NULL
LEFT JOIN ProductBrands    AS pb
        ON   pb.ProductGroup   = p.ProductGroup
        OR   pb.ProductBrand   = p.ProductBrand
INNER JOIN Prodcuts         AS pr
        ON   pr.ProductBrand   = pb.ProductBrand
        OR   pr.Product        = p.Product
        OR   COALESCE (p.ProductGroup, p.ProductBrand, p.Product) IS
NULL
INNER JOIN Orders           AS o
        ON   o.Customer        = c.Customer
        AND o.Area            = a.Area
        AND o.Product         = pr.Product
GROUP BY   o.OrderNo;

(All above queries are untested!)

--
Hugo Kornelis, SQL Server MVP

2 Sep 2006 5:28 AM

krislioe

Hi Hugo,
Thanks for your response. You open my mind about how the design should
be..
I have further question to make it easier to enter this discount into
the table. Here is additional info :

Usually the Promotion comes in this statement from Promotion manager :

Promotion No.999, For :
Customer Group : CG1, CG2
on Area : Area1, Area2, Area3, Area4
On these product
     prod001   2%
     prod002   3%
     prod003 1.5%    .

It means the rows that user must enter to Discount Table is : 2*4*3 =
24 rows
(cartesian product of 2 custgroups, 4 areas, and 3 products), isn't it
?

To avoid this, can we design the Discount Table into Master Detail :
one Master and Three details (for each triplet). Then we create a VIEW
from these master detail to produce the Discount Table View like your
original suggestion. Is this possible ?

CREATE TABLE PromotionHeader
              (PromotionNo int NOT NULL,
               PromotionName varchar(80) NOT NULL,
               PRIMARY KEY (PromotionNo));

CREATE TABLE PromotionDetailCustomer
              (PromotionNo int NOT NULL,
               LineNo numeric(5,3) NOT NULL,
               CustGroup int NULL,
               CustType int NULL,
               Customer int NULL,
               PRIMARY KEY (PromotionNo, LineNo ));

CREATE TABLE PromotionDetailArea
              (PromotionNo int NOT NULL,
               LineNo numeric(5,3) NOT NULL,
               Region int NULL,
               District int NULL,
               Area int NULL,
               PRIMARY KEY (PromotionNo, LineNo));

CREATE TABLE PromotionDetailProduct
              (PromotionNo int NOT NULL,
               LineNo numeric(5,3) NOT NULL,
               ProductGroup int NULL,
               ProductBrand int NULL,
               Product int NULL,
               PRIMARY KEY (PromotionNo, LineNo));

CREATE VIEW Promotions AS
SELECT ...*... FROM PromotionHeader
    INNER JOIN PromotionDetailCustomer ON..
    CROSS JOIN PromotionDetailArea ON..
    CROSS JOIN PromotionDetailProduct ON..

Can we do it this way to create more user friendly UI for users ?

Thank you very much,
xtanto

Hugo Kornelis menuliskan:
Show quote

> On 31 Aug 2006 08:47:54 -0700, krisl***@gmail.com wrote:
>
> >Hi All,
> >
> >We have a Discount scheme that can be given for all possible
> >combination of all
> >customer, area and product hierarchy (attached below).
> (snip)
> >The question is : I am looking for the efficient way to do this,
> >
> >1) How should I design the tables to accomodate all possible discount
> >combination above ?
> >2) How is the query to determine the total discount that an Order
> >should get ?
>
> Hi xtanto,
>
> You don't mention the CustGroups, CustTypes, Customers, Regions,
> Districts, Areas, ProductGroups, ProductBrands, and Products tables.
> I'll assume that you have all these, and that they are correctly
> normalized (so that CustTypes has a foreign key into CustGroups,
> Customers has a foreign key into CustTypes, Districts has a foreign key
> into Areas, etc.)
>
> I'll give you two solutions for this. Both share the same design for the
> discounts table:
>   CREATE TABLE Promotions
>               (PromotionNo int NOT NULL,
>                PromotionName varchar(80) NOT NULL,
>                Discount numeric(5,3) NOT NULL,
>                CustGroup int NULL,
>                CustType int NULL,
>                Customer int NULL,
>                Region int NULL,
>                District int NULL,
>                Area int NULL,
>                ProductGroup int NULL,
>                ProductBrand int NULL,
>                Product int NULL,
>                PRIMARY KEY (PromotionNo));
> You'll probably want to add foreign key constraints into the CustGroups,
> CustTypes, Customers, Regions, Districts, Areas, ProductGroups,
> ProductBrands, and Products tables. You'll also want to add check
> constraint to insure that at most one out of each triplet of criteria is
> not null. (They can all be NULL for all customers, all areas, and all
> products).
>
> In the first solution, you'd have a second table to hold the discounts
> for each possible combination of customer, area, and product. Something
> like this:
>   CREATE TABLE PromotionsHelper
>               (PromotionNo int NOT NULL,
>                PromotionName varchar(80) NOT NULL,
>                Discount numeric(5,3) NOT NULL,
>                Customer int NOT NULL,
>                Area int NOT NULL,
>                Product int NOT NULL,
>                PRIMARY KEY (Customer, Area, Product, PromotionNo));
>
> To keep this table current, use either of these methods, or a mixture:
> 1. Use triggers to change PromotionsHelper when any of the underlying
> tables change. Remember that you need triggers on Promotions, but also
> on the CustGroups, CustTypes, Customers, Regions, Districts, Areas,
> ProductGroups, ProductBrands, and Products tables. Note that these
> triggers can slow down operations on those tables.
> 2. Schedule a job to periodically rebuild the PromotionHelper table from
> scratch and accept stale data in between runs of that job.
> 3. (SQL Server 2005 only). Use Service Broker to asynchronously kick off
> a job to change PromotionsHelper from triggers on the underlying tables.
>
> Note that this table can grow very large if you have lots of customers,
> products, and areas. However, the query to find total discount for an
> order gets extremely simple (and fast, due to the PRIMARY KEY index):
>   SELECT     o.OrderNo, SUM(p.Discount)
>   FROM       Orders           AS o
>   INNER JOIN PromotionsHelper AS p
>         ON   p.Customer        = o.Customer
>         AND p.Area            = o.Area
>         AND p.Product         = o.Product
>   GROUP BY   o.OrderNo;
>
>
> The second solution dooesn't use an extra table. This means that the
> query to find total discount gets way more complicated. Something like
> this:
>
>   SELECT     o.OrderNo, SUM(p.Discount)
>   FROM       Promotions       AS p
>   LEFT JOIN CustTypes        AS ct
>         ON   ct.CustGroup      = p.CustGroup
>         OR   ct.CustType       = p.CustType
>   INNER JOIN Customers        AS c
>         ON   c.CustType        = ct.CustType
>         OR   c.Customer        = p.Customer
>         OR   COALESCE (p.Customer, p.CustType, p.CustGroup) IS NULL
>   LEFT JOIN Districts        AS d
>         ON   d.Region          = p.Region
>         OR   d.District        = p.District
>   INNER JOIN Areas            AS a
>         ON   a.District        = d.District
>         OR   a.Area            = p.Area
>         OR   COALESCE (p.Region, p.District, p.Area) IS NULL
>   LEFT JOIN ProductBrands    AS pb
>         ON   pb.ProductGroup   = p.ProductGroup
>         OR   pb.ProductBrand   = p.ProductBrand
>   INNER JOIN Prodcuts         AS pr
>         ON   pr.ProductBrand   = pb.ProductBrand
>         OR   pr.Product        = p.Product
>         OR   COALESCE (p.ProductGroup, p.ProductBrand, p.Product) IS
> NULL
>   INNER JOIN Orders           AS o
>         ON   o.Customer        = c.Customer
>         AND o.Area            = a.Area
>         AND o.Product         = pr.Product
>   GROUP BY   o.OrderNo;
>
> (All above queries are untested!)
>
> --
> Hugo Kornelis, SQL Server MVP

3 Sep 2006 8:10 PM

Hugo Kornelis

On 1 Sep 2006 22:28:25 -0700, krisl***@gmail.com wrote:

Show quote

>Hi Hugo,
>Thanks for your response. You open my mind about how the design should
>be..
>I have further question to make it easier to enter this discount into
>the table. Here is additional info :
>
>Usually the Promotion comes in this statement from Promotion manager :
>
>Promotion No.999, For :
> Customer Group : CG1, CG2
> on Area : Area1, Area2, Area3, Area4
> On these product
>     prod001   2%
>     prod002   3%
>     prod003 1.5%    .
>
>It means the rows that user must enter to Discount Table is : 2*4*3 =
>24 rows
>(cartesian product of 2 custgroups, 4 areas, and 3 products), isn't it
>?
>
>To avoid this, can we design the Discount Table into Master Detail :
>one Master and Three details (for each triplet). Then we create a VIEW
>from these master detail to produce the Discount Table View like your
>original suggestion. Is this possible ?
(snip)

Hi xtanto,

Looking at your design, I don't see any reason to expect major problems
(though performance might become one as your tables grow). However, I
think the reasons you give for choosing this design are rather weak. The
database design should not be dictated by UI but by data structure,
functional dependencies, normalization rules, and (only if required) by
performance considerations.

If I were the one tasked with entering the transaction details into the
database, I would expect to be using a well designed UI. Probably a
screen with either selection lists or checkboxes (depending on numbers)
for customer groups, areas, and products. Checkboxes allow for more than
one choice to be made; most selection lists as well (usually by holdng
down Ctrl while selecting choices). After entering the information on
the user-friendly entry screen, I'd double-check the information I
entered one final time, then click the "Accept" button and move on to my
next task. If the UI translates my request into one single INSERT
statement, 24 INSERT statements, or even a mixture of thousands of
INSERT, UPDATE, and DELETE statements, I couldn't care less.

So even though your design woould prboably work, my advice would be to
discard the idea and let the front-end handle the creation of 24 INSERT
statements from the promotion entry screen.

--
Hugo Kornelis, SQL Server MVP

4 Sep 2006 12:17 AM

krislioe

Hi Hugo,
Thanks for your response..

Ok, I get the point : we have to make a user friendly UI, but its the
UI itself that has to improvise to work with DB design, Not the DB
Design to be influenced by the UI.

What if like this ???
The design of discount table is like original design, but for UI will
work with temporary tables, and when user press SAVE, the UI logic
will INSERT the Discount table with the cartesian product (24 rows) of
the three temporary tables.

Thank you,
xtanto

Hugo Kornelis menuliskan:
Show quote

> On 1 Sep 2006 22:28:25 -0700, krisl***@gmail.com wrote:
>
> >Hi Hugo,
> >Thanks for your response. You open my mind about how the design should
> >be..
> >I have further question to make it easier to enter this discount into
> >the table. Here is additional info :
> >
> >Usually the Promotion comes in this statement from Promotion manager :
> >
> >Promotion No.999, For :
> > Customer Group : CG1, CG2
> > on Area : Area1, Area2, Area3, Area4
> > On these product
> >     prod001   2%
> >     prod002   3%
> >     prod003 1.5%    .
> >
> >It means the rows that user must enter to Discount Table is : 2*4*3 =
> >24 rows
> >(cartesian product of 2 custgroups, 4 areas, and 3 products), isn't it
> >?
> >
> >To avoid this, can we design the Discount Table into Master Detail :
> >one Master and Three details (for each triplet). Then we create a VIEW
> >from these master detail to produce the Discount Table View like your
> >original suggestion. Is this possible ?
> (snip)
>
> Hi xtanto,
>
> Looking at your design, I don't see any reason to expect major problems
> (though performance might become one as your tables grow). However, I
> think the reasons you give for choosing this design are rather weak. The
> database design should not be dictated by UI but by data structure,
> functional dependencies, normalization rules, and (only if required) by
> performance considerations.
>
> If I were the one tasked with entering the transaction details into the
> database, I would expect to be using a well designed UI. Probably a
> screen with either selection lists or checkboxes (depending on numbers)
> for customer groups, areas, and products. Checkboxes allow for more than
> one choice to be made; most selection lists as well (usually by holdng
> down Ctrl while selecting choices). After entering the information on
> the user-friendly entry screen, I'd double-check the information I
> entered one final time, then click the "Accept" button and move on to my
> next task. If the UI translates my request into one single INSERT
> statement, 24 INSERT statements, or even a mixture of thousands of
> INSERT, UPDATE, and DELETE statements, I couldn't care less.
>
> So even though your design woould prboably work, my advice would be to
> discard the idea and let the front-end handle the creation of 24 INSERT
> statements from the promotion entry screen.
>
> --
> Hugo Kornelis, SQL Server MVP

4 Sep 2006 10:49 PM

Hugo Kornelis

On 3 Sep 2006 17:17:48 -0700, krisl***@gmail.com wrote:

>Hi Hugo,
>Thanks for your response..
>
>Ok, I get the point : we have to make a user friendly UI, but its the
>UI itself that has to improvise to work with DB design, Not the DB
>Design to be influenced by the UI.
>
>What if like this ???
>The design of discount table is like original design, but for UI will
>work with temporary tables, and when user press SAVE, the UI logic
>will INSERT the Discount table with the cartesian product (24 rows) of
>the three temporary tables.

Hi xtanto,

Yep, that would be one way of doing it.

However, most programming languages used in the front end support
arrays. Wouldn't it be better to store the information entered locally,
in an array, instead of constantly reading to and writing from scratch
tables in the database. Minimizing database load and (sometimes even
more important) network traffic can be great for salability!

--
Hugo Kornelis, SQL Server MVP