Table Design Discussion

[ecobb]

Hi,

I'm working on an application to track all of our sales quotes and I'm trying to figure out how I want to handle a certain table in the database.

Basically, here's the scenario: Whenever someone in sales needs to quote a price to a customer, they'll use this application to create the quote. We have a list of 87 materials (another table in the database) with the standard price for each material. When a quote is generated, you have the option to change any of the material prices for that quote. So, for every quote generated, I will be storing a list of all 87 materials and prices for that quote.

So, here's my question: Would it be better to create a table with columns for all 87 prices so that I have 1 record with all of the quote information, or should I create a smaller table to store each individual price for the quote and have 87 price records for that quote?

I've been wrestling with this for a day or so, and here are some of the pro's and con's I've come up with. To start with, one record with 87 columns means that every time we add a new product/material I'll have to add a new column to the table. Yuck! But, storing each individual price means that for every 1 quote created I'll have to do 87 inserts into the smaller table...kinda nasty trying to do the inserts and the table would grow like crazy, but it would simplify the whole "add new product/material" situation.

What are your thoughts on this situation?

Thanks!


Hope This Helps!

Ecobb

"My work is a game, a very serious game." - M.C. Escher

 

[SQLSister]

Do you really have all 87 items in a quote? Every time?

WE have a proposals database that we use to prepare sales proposals. We have a product table with all the possible products entered in (which in our case are thousands of them from hundreds of differnt vendors.) THen each part is stored as it is selected for use in the proposal. ANd yes we store a ll the data, price etc, even if we do not change the price as we don't want an existing proposal to be changed if someone changed the base price from a new price list.)

I'll warn ya, this sales proposal stuff can get really complicated. We have to figure adminstrative costs for each section of the proposal and the stored procedure to do that is 19 pages long and it's one giant select with lots of calculations. And then there are the umpteen differnt ways they want to send the data to the client or view it themsielves. Leads to lots of report building becasue of course, different clients have different reporting needs (We do a lot of bids on contracts which often have specific formats defined.) So I'm of the normalize the table school. It may 87 parts now, but it will be 91 next year and 210 the year after that.

YOu might want to archive old proposals to cut down onthe number of records you have to search through to breing up current data.

One thing that has helped save our sales guys time is that we have a template built which sets up the proposal with the parts we usually use to bid on a particular type of job. Easier to create a proposal based on a template and then adjust it, than to enter 200 parts yourself.



Questions about posting. See faq183-874

 

[lespaul]

You seem to be saying that every customer always gets a quote for every piece of material you sell? That's amazing!

A sales quote is really just an pre-invoice to the seller, and should be set up like an invoice:

tblSalesQuote
QuoteID
CustomerID (FK to tblCustomer)
QuoteDate

tblQuoteDetails
QuoteID (FK to tblSalesQuote.QuoteID)
LineNumber (composite PK of QuoteID and LineNumber)
ItemID
QuotedPrice
QuotedQuantity

So, if every customer does actually get a quote on every item, then yes you will have 87 records for each quote. So?

You may also want to have in tblSalesQuote an indicator if a sale was made and store the date it was converted to an invoice.

HTH



Leslie

 

[lespaul]

Great minds think alike SQLSister!

Leslie

 

[mrdenny]

I don't see a need to store all 87 items every time, if those records aren't needed. If the value is 0, then don't bother to store the record.

I would deffinetly store the item data in a different table then the main body of the quote. Creating tables that you will have to modify if you add or remove items from the database is bad database design, and it violates the higher forms of normalization.

It is also a major pain when you have to add items in, and you have to kick everyone off the system, and test a bunch of code just to be able to see another product.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[willir]

Ecobb

Having 87 fields, one for each price is not very "normalized". Problems from this approach ...
- You would have to remember which column for which product through your application.
- Each new product would require the addition of a new field AND going through and modifying all related queries, forms, reports to accommodate the change.

A more normalized approach would have a pricing table with one record for each price.

Now, you indicate the sales rep can change the standard price. Do you have certain required volumes, authorizations, percentage limitations etc when the rep changes the price.

For example, if the rep cuts the price to 80% but sells 100 units; another rep sells at 90% and sells 1000 units.

tblPrice
PriceID - primary key
MaterialID - foreign key material table
Price - currency
RevisionDate - date

Discussion:
I am not sure if your foreign key is numeric or character based - adjust accordingly.

The revision data indicates when the price was last changed. Ideally, the system should make this change. You can even audit the process and capture who made the change.

Now, if you have restrictions on pricing, some can be added to this table. For example..

MaxDiscountPrice - decimal

Other pricing restrictions such as required sales volumes...

tblPriceDiscount
PriceDiscountID - primary key
PriceID - foreign key to Pricing table
MaterialID - foreign key material table
PriceDiscount - decimal
RequiredVol - interger
Mandatory - yes / no

Discussion:
If you decide to use this approach, you can check to make sure the sales rep price meets this requriement. Specifically, their price is xx% of standard, and their volume is yy. Stripped down SQL...
SELECT MAX(PriceDiscount)
WHERE RequiredVol <= Sales

Mandatory would be if No / False if the Sales Rep could over rider the guideline.

...Moving on
The one thing you do want to capture is what did I charge the customer for this product the last time I sold it to them, and how much did they buy, and when did they buy. Otherwise, you may get some pretty upset customers, especially when one rep is on vacation or unavailable, and their fill-in does not have a clue on past history.

tblSalesHistory
SalesHistoryID - primary key
MaterialID - foreign key to material table
CustomerID - foreign key to customer table
SalesRepID - foreign key to Sales Rep table
PriceID - foreign key to pricing table
SalesPrice - currency, actual price given
SalesQty - interger
PercentOfStandard - decimal
DateOfDale - date
Comments - text or memo

Discussion:
This table can become a very powerful tool for analysis. It can provide average price and discount for materials, for sales rep., and for customer, and review price discount vs quantity, etc.

The only thing not discussed is if you want to track price history - what was the previous prices, review which prices are very dynamic and those that are static, etc.

Richard

 

[ecobb]

Hey, thanks for the replies!

I agree that it should probably be split up into individual prices in the table. One of the main reasons for storing all 87 prices for each quote is, as SQLSister mentioned, we don't want the previous quotes to be changed if someone changes the base price list. I have had some concerns about this, as I really don't see the need to save all 87 of them, but my boss seems to think we need to.

I hate to cut this short, but I'm on my to a meeting with the dept. that will actually be using this. I've got a bunch of questions I need answered.

Thanks!



Hope This Helps!

Ecobb

&quot;My work is a game, a very serious game.&quot; - M.C. Escher

 

[SQLSister]

Since you are giving them the right to make changes, suggest you set a % of price change that they can make without approval. Our sales guys will give away the farm to get their commission, not caring if we can't do the job for that. The way our systems works, we have the price we pay for the part (if you manufacture it, the cost to make the thing). Then we have a markup field which has a default value of 1.5. The sales guys can change this, but can't go below 1 (or the cost of the product.) If we have to go belowe that for a particular product, I can override it it the database system, but the CEO or company President has to tell me to. SAme thing with labor hours, accounting tells us how much we have to mark up a person's labor to makes sure benefits and overhaed are covered.

Questions about posting. See faq183-874

 

[ecobb]

In our little meeting this morning, it turns out that we (my boss) had the wrong impression of what the Sales Dept. was trying to do. Actually, the Sales Dept. had the wrong impression of what they were trying to do, too. It was the people in Accounting that straightened everyone out.

By default, everyone will be charged from the base price list, and a sales quote will be filled out ONLY if there is a price change and it should ONLY record the price changes. Which is what I (and all of you) felt like it should be doing. We also found a few other problems which, like this one, stem from a massive lack of communtication. Oh, well...


That's a good point SQLSister, and I actually brought that up in the meeting. It seems that they are allowed to make price cuts at their discression, with no set percentages (although I'm sure they have some type of limits from their managers). So, I'm going to build it like that and let them enter $0 if they want to. ;-)

There seems to be more politics than common sence around here, so I'm frequently thrown into "closed minded" situations like this.

Thanks!



Hope This Helps!

Ecobb

&quot;My work is a game, a very serious game.&quot; - M.C. Escher

 

[ceh4702]

Maybe you should tell the sales people the more they cut on the price the lower their commission is.

If you do not like my post feel free to point out your opinion or my errors.