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.