ArtenSUITE
A Development Journal
Part 6: Pricing
This is not an article about pricing a software product. This is an article about the design of a flexible and powerful customer/product pricing ‘system’ that I intend to implement within the sales order / invoicing module of ArtenSUITE.
(Table and Column names are examples only)
Having written and used and worked with many MIS / ERP / Finance systems over the years I have never created or seen one that handled pricing in a way that I was 100% happy with. This article covers my current thoughts regarding a pricing ‘system’ that I will tweak and modify mentally for a while before I commit anything to code.
ArtenSUITE is a long term project and I am only just starting with the design and creation of the base module. There is no pressing deadline so I have the time to think carefully about each portion of the product and try and make it the best it can be.
Stage 1
Each line of the ORDER_LINE table will be checked against the PRODUCT_PRICE table. The PRODUCT_PRICE table looks like this:
Primary Key
Insert Date
Product Table PK
Start Date
End Date
Price
Using the SYSDATE (or similar) function the PRODUCT_PRICE table is queried to find the price that is currently applicable. The latest record inserted in which SYSDATE is between Start Date and End Date is used to determine the price.
This means that price adjustments, such as a yearly increase, can be created and stored in the database well in advance and yet not affect any orders until the date arrives for the scheduled pricing update.
Stage 1
Each line of the ORDER_LINE table will be checked against the PRODUCT_PRICE table. The PRODUCT_PRICE table looks like this:
Primary Key
Insert Date
Product Table PK
Start Date
End Date
Price
Using the SYSDATE (or similar) function the PRODUCT_PRICE table is queried to find the price that is currently applicable. The latest record inserted in which SYSDATE is between Start Date and End Date is used to determine the price.
This means that price adjustments, such as a yearly increase, can be created and stored in the database well in advance and yet not affect any orders until the date arrives for the scheduled pricing update.
It also means that you could do a ‘Special July Offer - All Prices Slashed’ type sale just by inserting a new set of pricing records with the Start and End dates being the beginning and end of July respectively.
A desirable side effect of this method is that a permanent pricing history is always available. Note: If a record is not found then a value of 0.00 will be inserted as the price.
Stage 2
The DISCOUNT column that is stored against the CUSTOMER record. This column represents the standard discount that will be applied for this customer for every purchase.
The price of each entry in the ORDERLINE table will be reduced by the percentage stored against the customer. This covers the scenario where different customers pay a different price dependent on whether they are an end user, distributor, dealer etc.
Stage 3
This stage covers the scenario whereby you wish to give the customer a further discount either as a percentage or a currency value, based on their turnover. This turnover can be either rolling X periods, where the period can be either day, week or month or alternatively the turnover can be between a Start and End date, maybe the Start and End of your financial year ? These turnover values could be derived by applying a SUM function on (Invoices - Credit Notes) for example.
This works as follows: Once the customer discount has been applied the CUSTOMER_TURNOVER_DISCOUNT table is queried. This table looks similar to this:
Primary Key
A desirable side effect of this method is that a permanent pricing history is always available. Note: If a record is not found then a value of 0.00 will be inserted as the price.
Stage 2
The DISCOUNT column that is stored against the CUSTOMER record. This column represents the standard discount that will be applied for this customer for every purchase.
The price of each entry in the ORDERLINE table will be reduced by the percentage stored against the customer. This covers the scenario where different customers pay a different price dependent on whether they are an end user, distributor, dealer etc.
Stage 3
This stage covers the scenario whereby you wish to give the customer a further discount either as a percentage or a currency value, based on their turnover. This turnover can be either rolling X periods, where the period can be either day, week or month or alternatively the turnover can be between a Start and End date, maybe the Start and End of your financial year ? These turnover values could be derived by applying a SUM function on (Invoices - Credit Notes) for example.
This works as follows: Once the customer discount has been applied the CUSTOMER_TURNOVER_DISCOUNT table is queried. This table looks similar to this:
Primary Key
Customer PK
Type (1 = Rolling, 2 = Static)
Number of Rolling Periods
Period Type (Day / Week / Month)
Start Date (For Use if Static)
End Date (For Use if Static)
Discount (Positive Discount = %, Negative Discount = Currency Value)
Threshold (Turnover Value)
The system looks first for ROLLING records for the customer. The latest record inserted is used to calculate the additional discount percentage or value to be applied to the product price. For example the customer may receive an extra 2% discount if their turnover in any given rolling quarter is more than £10000. The record would look like this:
Primary Key = 123789
Type (1 = Rolling, 2 = Static)
Number of Rolling Periods
Period Type (Day / Week / Month)
Start Date (For Use if Static)
End Date (For Use if Static)
Discount (Positive Discount = %, Negative Discount = Currency Value)
Threshold (Turnover Value)
The system looks first for ROLLING records for the customer. The latest record inserted is used to calculate the additional discount percentage or value to be applied to the product price. For example the customer may receive an extra 2% discount if their turnover in any given rolling quarter is more than £10000. The record would look like this:
Primary Key = 123789
Customer PK = 'SJC001'
Type = 1
Number of Rolling Periods = 3
Period Type = Month
Start Date = Null
End Date = Null
Discount = 2.00
Threshold = 10000
The system then looks for STATIC records for the customer. The latest record inserted is used to calculate the additional discount percentage or value to be applied to the product price. For example the customer may receive an extra 3% discount if their turnover this financial year is more than £150000. The record would look like this:
Primary Key = 123790
Type = 1
Number of Rolling Periods = 3
Period Type = Month
Start Date = Null
End Date = Null
Discount = 2.00
Threshold = 10000
The system then looks for STATIC records for the customer. The latest record inserted is used to calculate the additional discount percentage or value to be applied to the product price. For example the customer may receive an extra 3% discount if their turnover this financial year is more than £150000. The record would look like this:
Primary Key = 123790
Customer PK = 'SJC001'
Type = 2
Number of Rolling Periods = Null
Period Type = Null
Start Date = ‘01 APR 2008’
End Date = ‘31 MAR 2009’
Discount = 3.00
Threshold = 150000
So you can give your customer a discount incentive for both rolling turnover and static turnover, or just one or the other. The key is flexibility. Note: If a CUSTOMER_TURNOVER_DISCOUNT record is not found, or the accumulated turnover did not reach the predetermined levels, then no additional discount is applied at this stage.
Stage 4
The final stage is where you may wish to give additional discount to a particular customer when they purchase a particular product, or when they purchase a particular quantity (or more) of a particular product.
This works by querying a Customer / Product Matrix table. The CUST_PROD_MATRIX table looks like this:
Primary Key
Customer PK
Product PK
Discount (Positive Discount = %, Negative Discount = Currency Value)
Quantity
The system checks for the existence of matrix records that match the customer and the product. If it finds one (or more) it checks the quantity and selects the appropriate additional discount from the record with the highest quantity that is less than or equal to the quantity entered on the order line.
If a CUST_PROD_MATRIX record is not found then no additional discount is applied at this stage.
And thats it - so far. It covers most pricing eventualities that I can recall at the moment. It is at this stage theoretical and has not yet been implemented in code, although it will be and thankfully that is the easy bit :-)
Any feedback, queries etc. please feel free to leave a comment.
"After silence, that which comes nearest to expressing the inexpressible is music." - Aldous Huxley
Type = 2
Number of Rolling Periods = Null
Period Type = Null
Start Date = ‘01 APR 2008’
End Date = ‘31 MAR 2009’
Discount = 3.00
Threshold = 150000
So you can give your customer a discount incentive for both rolling turnover and static turnover, or just one or the other. The key is flexibility. Note: If a CUSTOMER_TURNOVER_DISCOUNT record is not found, or the accumulated turnover did not reach the predetermined levels, then no additional discount is applied at this stage.
Stage 4
The final stage is where you may wish to give additional discount to a particular customer when they purchase a particular product, or when they purchase a particular quantity (or more) of a particular product.
This works by querying a Customer / Product Matrix table. The CUST_PROD_MATRIX table looks like this:
Primary Key
Customer PK
Product PK
Discount (Positive Discount = %, Negative Discount = Currency Value)
Quantity
The system checks for the existence of matrix records that match the customer and the product. If it finds one (or more) it checks the quantity and selects the appropriate additional discount from the record with the highest quantity that is less than or equal to the quantity entered on the order line.
If a CUST_PROD_MATRIX record is not found then no additional discount is applied at this stage.
And thats it - so far. It covers most pricing eventualities that I can recall at the moment. It is at this stage theoretical and has not yet been implemented in code, although it will be and thankfully that is the easy bit :-)
Any feedback, queries etc. please feel free to leave a comment.
"After silence, that which comes nearest to expressing the inexpressible is music." - Aldous Huxley
www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business
No comments:
Post a Comment