Table design for Inventory Management

[WB]

I have an Inventory table that has many basic attributes such as Id,
description, Price1, Price2, Price3, category, etc.

The model is now changing and I want to provide for multiple price levels
and discounts that will start and end on a schedule such as:
buy X get Y free
buy one get one half off

I realize my application will have to loop through some algorithms to handle
business rules, but I am struggling with how the data modal (Tables) will be
defined to store this information.

any samples or suggestions are greatly appreciated.

WB

 

[Allen Browne]

There are several possible structures you might consider.

The one below copes with defining a "special", available from a date,
optionally with an end date. To qualify for the special, the purchaser must
order all products in the SpecialCondition, which might be one of some
product at a special price, or it might be 10 of the product, or it might be
a package (combination of quantities of several products.)

For a simple special, that's all there is to it. But you might want to offer
free or reduced price bonus product(s) as well. For that kind of special,
you will have related entries in a SpecialBonus table as well.

So now you have these tables and fields:

Special table: one record for each advertised special. Fields:
SpecialID primary key
StartDate first date this is available. Required
EndDate last date this is available. Null if open-ended.

SpecialCondition: a record for each product in a special. Fields:
SpecialID foreign key to Special.SpecialID
Quantity number of this item you must buy to qualify. >= 1
ProductID which product you must buy to qualify.
PriceFactor Between 0 and 1 times the usual product price.

SpecialBonus: a record for each bonus given with a special. Fields:
SpecialID foreign key to Special.SpecialID
Quantity quantity of bonus product you also get.
ProductID which bonus product you get with this special.
PriceFactor Between 0 and 1 times the usual product price (0 = free).

That should cope with most scenarios. If necessary, you could replace the
PriceFactor field with a pair of fields so you could fix the price for the
special, regardless of fluctuations in the normal price of the product:
ValueTypeID "Factor", "PriceEach", "DiscountAmountEach"
Value amount of the factor / priceeach / discount amount.

Hopefully that's enough that you can adapt it to your scenario.