The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Configuring a database where the price changes rapidly?

Okay, I'm running into a slight issue with a SQL Server 2005 database.  Our products are loaded onto several third-party venues, in addition to our own customer-facing site.  We receive orders from all of these, and my predecessor set up some VBScript routines that parse data (most of which is EDI format) and places it into our main production database.  Each of these can have different prices, or the same prices, or a combination of both depending on the COO's analysis.

The issue at hand is that our pricing structure typically changes every few weeks due to our main customer being the federal government and us using "Temp Pricing" to remain competitive.  So if Item A is $24.65 today in our system and on the government site, it might be $18.74 next week if temp pricing kicks in.  Our back-end system has the normal, non-temp pricing as well as the retail and list prices.  The way my predecessor took into account changing prices is to store everything the customer looks at and adds to their cart in separate tables - there's "cartRows", "dbSession", and "dbSessionCart", of which cartRows captures the price of the item at the time it was ordered.  The main issue here is that most of the ASP pages in our back-end ERP have to make upwards of 15 separate database calls (done in inline SQL, of course since it will retrieve the customer ID, then look up the session ID, then get the cart ID, then get items in the cart, then look up the item details, etc. not to mention getting "application settings" like the database connection string in every page) per request, and some pages look in different columns - for example, at least one page won't look at cartRows, but will look at the "official" price of the product as listed in our products table.  Another will look at the "price" column, which is supposed to be the RETAIL price, instead of "bTobPrice" which is supposed to be the normal, non-temp government price (which forces me to do a hack-fix and set them equal , otherwise the price will be 0.00).  And then a few pages will ONLY look at cartRows for the item, and ignore it if it's not listed there (whether or not it's listed as part of the order).

So I've been trying to come up with a better way to handle all of this, as the system has lately been breaking down and our employees have been complaining (not really complaining) to me to fix it - unfortunately for me, the code itself is really, really bad and 75% of the time reads like hieroglyphics due to my predecessor's cryptic naming schemes (There's mySQL for the queries, which isn't THAT bad, but then there's rtmp_2, custnumb, aryitmlsts, and the like.  Also the most important module in the system makes use of  some kind of Java applet Remote Procedure call to pretend it's Ajax, and every single field (there are about 100 input fields, all hard-coded) has inline javascript doing something or another).

I've been meaning to push for a rewrite, or at least improving the data model itself to follow normalization rules and impose data integrity (For example:  -1 is used for True, 0 is used for False, *EXCEPT* in one critical area where 0 is True and 1 is False.  Also, customer and order data is repeated in at least five tables, and sometimes it's different in each table.  The order details are kept in a single ntext field, separated with HTML <br> tags, which presumably the application parses at some point.  You get the idea, I'm sure).

Anyways, the main issue of concern is the price structure.  There *has* to be a better way than this, right?  It would not be so bad if the price didn't change often - we need to change pricing every 3 weeks or so on some products (sometimes all of them, sometimes not), and every 3 months on *all* products, so I can't just pull the most recent price from the database because the customer might have placed the order when there was temp pricing, and we cannot charge them a different price than it was when they placed the order.  The current way just seems very convoluted and inefficient, so I'm hoping to get some input on a better way; of course I'm not certain if I can implement a better way because the whole application works off of the assumption you're using the current implementation, but it would at least be a step in the right direction.
WayneM. Send private email
Wednesday, March 19, 2008
If the solution for improving it is not obvious, then you must not attempt any significant re-work. Everything you explained about how the price at purchase is captured sounds correct to me. Using the database select statements to cross-reference the user ID to the session and pricing sounds correct too. I don't get the part about forcing the prices to be the same. Resist the urge to do over when there are complex requirements like these since you will also be faced with migrating the current schema to support purchases in progress. Who is to say you will understand the needs such that the inevitable pains of swapping in a new schema will be worth it? If you want to improve the system, take small isolatable steps, renaming variables, restructuring small pieces of code to prove you thoroughly understand the system, and look at improving the performance of your SQL.
Ben Bryant Send private email
Wednesday, March 19, 2008
The issue with prices being the same is as follows, roughly:

We have invoices, orders, purchase orders, etc. that we print out and need to file.  Because the current system stores the price data in five different databases, the data is inconsistent depending on which invoice we try to print.  For example, today a purchase order was displaying only one of the items in a customer's purchase, because it was *only* pulling from the CartRows table (we give customers quotes as well, so in addition to ordering from one of the stores, they can call in and we can work a deal with them, which is entered manually into the ERP system), while the invoice was displaying both because it was parsing the data contained in the Orders table.

This is what the issue is, however I agree with your comment about attempting to refactor some here and there.  Unfortunately for me, there are about 12,000 files so it would be impossible to refactor all of them to the extent necessary.
WayneM. Send private email
Wednesday, March 19, 2008
Yes I think your only option is small incremental improvements where possible. The changing list price, estimate, quote, unbundled, and even international conversion rates, make pricing, invoicing, not to mention revenue reporting extremely complicated. On first look I see 2 guidelines falling out: 1) capture all the information at each user/customer transaction, and 2) never confuse current list prices with the actual prices the user/customer is working with.
Ben Bryant Send private email
Wednesday, March 19, 2008
You probably have some form of effective-dating on your prices, right, so you can tell what the price was on such-in-such date?  You could conceivably use something like that to derive the price on a given date, but I wouldn't.

It's always nice to be able to have the exact price a customer paid, regardless of how that price was computed.  For instance, I've seen scenarios where other factors could influence price - an allocated discount, or a regional price, or an adjustment given by a salesperson - whatever. 

In any event, the last thing you want is to end up reprinting an invoice and discover all of a sudden that your line-items don't add up to the bottom-line price anymore because the prices of items has changed.  It's worth the extra couple of fields to store the "as sold" values, IMO.
D. Lambert Send private email
Wednesday, March 19, 2008
Even if you don't keep the effective dates, I've done something like this:

Product (current price)

Cart (no price stored)

Quote (price defaults to product current price at time quote was created from cart, but can be altered)

Order Line (product price at time submit of cart or quote was clicked)

In a system where quotes aren't needed, you go straight from cart to order line.

Invoices can be created from the orders - you can either allow change to orders before fulfillment, or whatever, but ultimately, this is the minimum pricing approach you usually need.

Some issues remain like price changes on products in people's carts (post messages or whatever), but that's your basic pricing model.
Cade Roux Send private email
Wednesday, March 19, 2008
"You probably have some form of effective-dating on your prices, right, so you can tell what the price was on such-in-such date?  You could conceivably use something like that to derive the price on a given date, but I wouldn't."

Not as far as I can tell.  The old guy was an Access developer - half the tables look like they were probably just exported from Access to Excel, and then imported into SQL Server.  There's no dating to see the past pricing history of an item, if it was captured at $7.56, but now it's unit of measure changed and it's now $45.50 (say, it was for one item but now it's for a pack of 12), there's going to be a huge gap in pricing between the invoice and the purchase order, with no obvious reason why (although the rest of the company is used to it and it gets dismissed as a unit change).

I'll have to delve deeper and see if there's a better way to do it - the current way seems like a nice idea but a worthless implementation (since it fails to achieve its result).
WayneM. Send private email
Wednesday, March 19, 2008
I recommend you take a little time to read this book:

It is out of print, so the author has made it available online. One could store the prices as they were available at a certain period of time in the past (see "state tables" - chap 5).
Peter Send private email
Wednesday, March 19, 2008
BTW, this is a good time to use triggers on the changing tables to get a handle on what's going on:

1. You already have a database which has a fragile design
2. The DB is losing data due to changes which are not accommodated in the design

Make a trigger on the relevant product table and write the changes to a log.  This will give you a handle on the historical prices.  Whether you use this log operationally or not, at least you'll get an idea of which ones are changing.
Cade Roux Send private email
Wednesday, March 19, 2008
I developed a sales system some time back and based the pricing data on SAP data structured (speak to a SAP MM or SD consultant om how pricing conditions work). Have not looked back.

The main advantage was consistency and common API.
Liam Send private email
Friday, March 21, 2008
+1 For effective Dates.

There should be 1 and only 1 list.

Any adjustments to the list price will be line items in the customers order. So that ultimately you can reconcile back to the list price at any time in history.

All the best.
Friday, March 21, 2008
I'll take a look at what I can do - thanks for the advice!  My only concern is that we have a "quote system" which is basically a data entry form that pulls in the data (it's the primary application in use).  There are some VBScripts somewhere that manipulate EDI data given by our government customers (GSAAdvantage), and does some weird calculations (the code is heavily obfuscated, or the developer was just a piss-poor programmer, I'm not sure which).  I know that this system pulls in from the main products table, but can be overridden.

What I am afraid of is that there won't be any time to sit down and rework this thing to function properly, as every change seems to bring more "Oops, forgot you have to do this too" moments out of the woodwork.  It would also mean redesigning the quote application (everything was written by the same guy, so it's all really obscurely written VBScript that relies on On Error Resume Next on every page to even work properly), which is something on my plate to look at, but not likely to get done anytime soon.

In any event, I've gotten some more information so I can make an informed decision.  Thanks again, everyone.

MIS Director
WayneM. Send private email
Friday, March 21, 2008
Even if price data is based on effective dates, it should still be straightforward to override those prices using a coupon on the individual orders.

That way, you'll always know the *real* price, the *discounted price*, and (by extension) the amount of discount given for each item on each order.

Having multiple independent price tables (with no history) sounds like absolute madness.
BenjiSmith Send private email
Saturday, March 22, 2008

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz