troubleshooting Question

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

Avatar of gw5434
gw5434 asked on
Microsoft SQL ServerDatabasesMySQL Server
2 Comments2 Solutions3507 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
Daniel Wilson

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros