How to make a markup table
-
I have a table in my database that is a base table for my blind companys. Each company can share the same price table.
(This price table only hase 5 columns in it)
1. BlindSizeID(KEY)
2. Maxheight
3. Price
4. CompanyID (FK)
5. ProductID (This one might take a hike)
Anyway, i am connection these to a set of dropdowns that are on the UI.
For instance one Dropdown will be the width and one will be the Height and One will be the company (Blind Maker Of Choice (Thier options of company)).
Here is my question they all can sell the same product; however, their priceing is all eaither a little higher or a little lower than each other. Is it possiable (Without recreating the wheel) to add or subtract the result that is returned from the stored query?
Should i do this all in the database or just the application..??
What if applyed a markup based on the company, then the best approach might be to have a table that describes markups.
my stored proc could take that table into consideration, and accept the companyID as a parameter. it would look up the markup and add that to the price(s) before returning the result.
I DO know that markups can be expressed in many ways: fixed-fee or variable rate are two examples. fixed-fee is where you'd have $5 markup per unit or portion, and variable rate is total_cost * markup_percentage. I also know that it is very important to model these a little before i start delving into it.
I AM looking for all suggestions and would really like an examlple if possiable.
Thank you,
Erik
Dam again!
-
It is great to see someone planning a database rather than jumping with the 'Ready, Fire, Aim' approach.
If I understand the question, each user will select a product, width, and height. These values will be submitted along with the company ID to your stored procedure. The stored procedure will then return the price. Correct?
If so, a separate 'markup' table sounds like the way to go. This builds in flexibility and allows for easy maintenance by users so a DBA doesn't have to maintain the markup rules and values. It seems to be most efficient to let the stored procedure do the work rather than the application. Easier to maintian, especially if you have more than one app using the data.
Does the markup type (per unit or percentage) apply to all products for all companies? Could Company A have a 5% markup on Product 1 and a $5.00/unit markup on Product 2?
-
Thank you for your responce!
Since what i am going to be using this solution for 1000's of different blinds, shutters, verticals, and how knows what else, i belive that a percentage would play a much better role in for this secenario. (I am open to any suggestion that you may have, and warmly welcome them also).
This size price, markup table i have been working on it for a while tring to get as much information as i can for it before i start; however, there were a couple of times that i thought i was ready, and quickly found out that i was not
. With that said what is your secenario for this solution?
Thank you for your help!
Dam again!
-
Now that i am over looking your question; (
Does the markup type (per unit or percentage) apply to all products for all companies? Could Company A have a 5% markup on Product 1 and a $5.00/unit markup on Product 2?
)
Yes it might be possiable that they do have a 5% markup on Product 1 and a $5.oo/Unit markup on Prodcut 2. Does this mean that i should seperate these two tables?
Thanks!
Erik
Dam again!
-
Definitly split them in 2 fields.
One for pourcentage : decimal (4,3), so a 5% increase would be entered as 1.05, 10% decrease would be 0.9.
Then one for amount : deicmal (?, 2)
Then you can calculate the price like : BasePrice * Pourcentage + amount.
With this formula I think you can guess that these 2 fields need defaults. 1 for pourcent and 0 for amount. You might also want to add a check constraint on the pourcentage field so that no one can enter 9.99 >> 999% increase or 0% >> free.
-
I am going to have to study this a bi; however, i will be in touch in a day or so.
I am extreamly grealful for your help!
Thanks,
Erik
Dam again!
-
HTH. But I'll be out for 3 days after tomorrow. You might not get fast answers on my part
. -
Erik,
I want to give this one a little thought too. I'll try to post something manana.
For clarification, do you forsee a possibility that an item could have both a percent markup and a dollar markup (i.e. 5% + $2.00)?
How will the prices and markups be entered and updated? Will you receive digital price lists form vendors? or will this be done manually by an employee?
See ya.
-
At this time i do not see a percent markup and a dollar markup.
And all of this will be enterd by an employee.
Thanks..
Dam again!
-
I used this formula because I didn't want to do :
BasePrice * Case when SomeCol is not null then SomeCol Else 1 end + Case when Col2 is not null then SomeCol2 else 0 end
Anyways I think that this version would take more time to calculate
. In my shop I often have a sitation where we add a percentage for the client, and also that we have some other transormation costs, so the price is actually changed twice in the same operation. However the situation is somewhat different for you and I don't know it this applies to your case. -
Will this app price only blinds and verticals? Anything like cabinets, or other products not tied to a length and width?
Do all companies sell all blinds? If not, maybe you will need to select the product form one set of tables, then get the pricing.
Does the height and length affect the price or are you returning a price-per-inch kind of value?
Will you want to show comparison prices such as a list for a given height/width with Company A's price and Company B's price, etc?
Remi - I like your formula. I too was thinking CASE but your approach is better. With two fields per record, you could have markup/discount and dollar cost for shipping, markup, whatever.
-
The list of possible added cost is long enough to give me a headache. I'll leave him to figure this one out
. -
Not all the companys sell blinds; therefore, I have a product table that has pricing for products EXCEPT for the blinds, shutter, Verticals, ect. they have a seperate table name the SizePrice table.
Comparing pricing between companys sounds very attractive and i like that idea; however, i am not sure that my brain can with stand that much for now.
The value is a per inch value.
Dam again!
-
Erik,
This is what I came up with last night. Hope it is in time to do you some good. I made assumptions about the actual data you will have, so it may need to be changed to fit your needs.
This setup allows you to:
a) Provide discount by entering a negative value in PercentMU
b) Place a percent,dollar (or both) value markup for each product
c) Enter a markup for some future period of time. On November 1 you might know that you want to markup (or discount) a specific product during the Christmas shopping season and return the markup to normal after Dec. 31. You can set this up on Nov 1 and forget it.
You can use the queries below within a stored procedure to return data to your application. As is, they return more info than you need but this shows what you can get with this configuration.
A couple of loose ends remain: You would want to put a trigger in the Markup table to make sure the BeginDate of one price is later than the previous EndDate for a product. The assumption is that if you have percent and dollar markup on a product, the percentage will be applied to the base price, then the dollar markup added to the result. If you want one type of markup but not the other, add a value to one field and leave the other set to 0 (zero is the default). Assumes that BlindSizeID is unique for each Company. This may not be true?
Copy and past this into Query Analyzer to see it run.
Good luck.
-- Set up example
-- Create tables
CREATE TABLE PriceTable
(BlindSizeID int NOT NULL,
CompanyID int NOT NULL,
MaxHeight int,
Price money,
ProductID int UNIQUE,
CONSTRAINT PK_Products PRIMARY KEY (BlindSizeID , CompanyID)
 
CREATE TABLE Markups
(ProductID int NOT NULL,
PercentMU decimal(6,2) DEFAULT 0,
DollarMU decimal (6,2) DEFAULT 0,
BegDate smalldatetime NOT NULL DEFAULT '1/1/2000',
EndDate smalldatetime DEFAULT '12/31/2078',
CONSTRAINT Markup_PK PRIMARY KEY (ProductID, BegDate),
CONSTRAINT Markup_Dollar_Positive Check( DollarMU >= 0),
CONSTRAINT Markup_EndDate Check(EndDate >= BegDate)
)
--Add records to PriceTable
INSERT INTO PriceTable VALUES(100,200,15,2.00,1234)
INSERT INTO PriceTable VALUES(101,300,20,2.50,2345)
INSERT INTO PriceTable VALUES(102,200,15,3.00,3456)
INSERT INTO PriceTable VALUES(103,300,15,4.50,4567)
INSERT INTO PriceTable VALUES(104,400,15,10.00,5678)
--Add records to Markups table
INSERT INTO Markups VALUES(1234, .1, 0.00,'7/4/2005','7/30/2005')
INSERT INTO Markups VALUES(1234, 0, 10.00,'8/1/2005','8/30/2005')
INSERT INTO Markups (ProductID, PercentMU, DollarMU) VALUES(2345, 0, 1.00)
INSERT INTO Markups (ProductID, PercentMU, DollarMU) VALUES(3456, .50, 1.00)
INSERT INTO Markups (ProductID, PercentMU, DollarMU) VALUES(4567, 0, 0)
INSERT INTO Markups VALUES(1234, -.1, 0.00,'9/1/2005','9/15/2005')
--Process records
DECLARE @Width int
DECLARE @Height int
DECLARE @CompanyID int
DECLARE @SaleDate as smalldatetime
SET @Width = 10
SET @Height = 12
SET @CompanyID = 300
SET @SaleDate = '7/5/2005'
--Returns records for all companies
-- Note that a sales date between 9/1 and 9/15 will return a discount
SELECT P.BlindSizeID, P.CompanyID, P.Price, P.ProductID,
M.PercentMU, M.DollarMU,
(P.Price * (1 + M.PercentMU) + M.DollarMU) AS SalesPrice
FROM PriceTable AS P
LEFT OUTER JOIN Markups AS M
ON M.ProductID = P.ProductID
WHERE P.MaxHeight >= @Height
AND @SaleDate BETWEEN M.BegDate and M.EndDate
ORDER BY SalesPrice
--Returns records for selected comapny
SELECT P.BlindSizeID, P.CompanyID, P.Price, P.ProductID,
M.PercentMU, M.DollarMU,
(P.Price * (1 + M.PercentMU) + M.DollarMU) AS SalesPrice
FROM PriceTable AS P
LEFT OUTER JOIN Markups AS M
ON M.ProductID = P.ProductID
WHERE P.MaxHeight >= @Height
AND @SaleDate BETWEEN M.BegDate and M.EndDate
and P.CompanyID = @CompanyID
ORDER BY SalesPrice
-
The editor replaced a closing parenthesis with a winking icon. You'll probably have to add it to get the Create Table statement to work.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply