We help IT Professionals succeed at work.

E-Commerce Database Schema Design - Gift Wrap, Discount Vouchers and Promotions

3,444 Views
Last Modified: 2008-07-24
Following on from a previous question about e-commerce schema design, is there a standard/recommended way to account for gift wrapping and gift vouchers?

For gift wrapping, it is not uncommon for a tickbox to be present on the checkout for users to select gift wrapping. Usually there is a price associated. As far as I can see, there are two options.

(NB Briefly, there is an "Orders" table and an associated "Order Lines" table for recording general order information and specific information about the products ordered, respectively.)

1. In the "Orders" table include a money/single field for a gift wrap price. If null or 0 (depending on setup) no gift wrap, if a positive amount then gift wrapping required.

2. Add gift wrapping in the "Products" table as a product. This is then added into the Order lines table as an Order Line/ Product. Has the advantage of being easily updatable by the user and allows multiple gift wrapping options. Disadvantages are that it is more complicated.

Which approach would be best? Or is there a "third way"?

For the vouchers, again there are a number of requirements/rules.

1. Vouchers should have a unique code and associated information such as expiry dates.
2. Vouchers should allow for absolute discounts (gift voucher) or percentage discounts (promotions)
3. There should be a facility to allow offers such as 2-for-1, free products and so on.
4. Vouchers should be set to "stackable" or "not stackable". For example a gift voucher could be used with another promotion, but promotions can only be used one at a time.

Obviously there will need to be some rules built into the business logic layer, but the database layer has to play its part too.

How should the vouchers table be structured? How should the discount be recorded? I am thinking something along the lines of having two columns, one for an absolute discount and another for a percentage discount, but this doesn't take into account some of the other requirements.

When vouchers are assigned to an order, I assume the most sensible thing is to have a separate "Order Vouchers" table, similar to the "Order Lines" table.
Comment
Watch Question

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.