Need Help Writing SQL To Apply Promotions to Shopping Basket at Checkout

Question

Hi All,

Don't laugh but I'm a Lotus Notes (non-relational database!) developer trying to work with SQL and, although I have the basic concepts nailed, I'm stuck on something I'd consider to be "advanced".

Imagine a user reaches an online checkout having added a set of products to their basket. I need to apply promotions to the basket.

These promotions look at the items in the basket and add "points" for any combination that matches a pre-defined "bundle". The promotions also need to be able to target users in specific countries (information gained at point of registration) and other personal details.

The promotions are entered and maintained by the site admin team and need to be as flexible as possible. So they can reward people for things like "Buy X products of type Y and get 50% extra points" or "3 or more XE-123s and get 500 points added" etc.

Right now I'm looking for general direction. How should I store the criteria that matches the items in a basket to any of the running promotions? Would one big Stored Procedure do or should the C# code that builds the basket loop through all promotions and see which fit?

Right now I don't even have a table schema. Just the knowledge of how it should work and little idea where to start.

Jake

Answer 1

'As flexible as possible' is a red flag (IMHO). I'd try to nail that down to:

• "Fixed-point and/or percentage (of total basket / bundle points) bonus (three columns in a helper table)
• When the basket contains a combination that matches a pre-defined 'bundle', where 'bundle' is contained in a helper table, with multiply rows, with a bundleID and a row for each item in the bundle, containing at least ItemID and Quantity.

And no other kinds of reward possible. This to keep the project / requirement manageable.

Then have a SP which checks for the presence of bundles within the basket and applies relevant promotions (as stored in the first helper table).

Also make sure you know the requirement whether 1 or multiple promotions are possible.

Answer 2

My suggestion is to not use SQL for this sort of business logic.

The database is a good place to keep information about products like whether they are type Y or type X. This keeps the database design pretty straightforward.

What you mention about C# seems like a better direction. There is a lot of searchable information about 3-tier architecture that can help explain the benefits of this strategy well.