Database Design for Limiting Coupon Use

Question

When working on introducing a voucher function for an e-commerce application, I need to implement a voucher usage restriction, some restrictions that I plan to have

• Products
• Exclude Products
• Product Categories
• Exclude Categories
• Email / Client Limitations

Currently, we support the following 2 types of vouchers with the possibility of creating a type of user voucher, and all these types of vouchers are supported in one table using a discriminator (using Hibernate).

• Sequential Vouchers
• Incentive vouchers.

These are just a few that I aimed at in the initial stage. The main confusion regarding database design and the limitations of these voucher usage with Voucher . I cannot decide which one is best to match these constraints in the database.

Should I go for one table for all these restrictions and be related to the Voucher table or is it useful to group all similar types of restrictions in one table and have them related to the voucher table.

As additional information, we use hibernate to map our objects to a DB table.

Answer 1

This seems like a very wide open and arbitrary demand. Some questions:

How complicated will the business rules you are trying to model be? If you allow (business) users to define their own vouchers, the chances are good, they will come up with some pretty Byzantine rules and combinations. If you need to support anything , they will have problems.

What will the database do with this data? Keep the “voucher definition” of course, but what then? Perform calculations or reports about them? Analyze how many of them are used, by whom / when / how / for what? Or just indicate what was used / created in the last year?

How much data will you have? One entry per voucher definition, or per voucher printed / issued? (If the latter, can you use one entry per voucher, with the number issued?) Do we say dozens, hundreds or millions of vouchers?

If its a completely free form, if they just want a listing without serious analysis, if the total volume is small, consider using blob fields rather than null oriented columns. Something like a large text field and a data entry window in which the user will "Enter any other criteria that define the voucher." (You might even do this using XML.) Awful, you cannot easily parse the data, but if the goals are too big or blurry and you are not going to use all this detailed data, it may be necessary.

Last note: a voucher that is good only for selected products cannot be used for products that are added after the creation of the voucher. A voucher that is good for all but selected products can be used for subsequently created products. This logic can be applied to any criteria restricting a voucher. Both methodologies have an advantage; make sure that users understand what they are doing.

Answer 2

If I understand what you are doing, you will have a problem with only one table for all constraints, because that means 1 row per Voucher and several values ​​in different constraint columns.

It will be more difficult for you to update, retrieve, and modify constraint values.

In my opinion, you should have one table for each type of constraint and map them to the Voucher table. However, it will be easier for you to add new restrictions.

Answer 3

I would personally go with the second sentence ... grouping all of these types of restrictions in one table that refers to the voucher table.

I will add to this that you can process, include and exclude in the same table.

So, the structure I would use is a few lines:

 Voucher (id, type, etc...) VoucherProductRestriction (id,voucher_id,product_id,include) VoucherProductCategoryRestriction (id,voucher_id,product_category_id,include) VoucherCustomerRestriction (id,voucher_id,customer_id) VoucherEmailRestriction (id,voucher_id,email) 

... where the include column may be boolean, which is true if you want to restrict the voucher for this product or category, or false if you want to limit it to any product or category other than those specified.

If I understand your context correctly, it makes no sense to include and exclude restrictions on the same voucher (although it makes sense to have more than one type). You can probably handle it and test it better if you use the same table for both types of constraints.

Answer 4

As a suggestion:

Isn't it wiser to have valid-products and valid-categories instead of Exclude-products and Exclude-categories ?
Having the Customer-Creditgroup will lead us to the valid-customer-group table.

By the way, in the current project we can have a voucher definition table, let's call it a voucher-type table.
About restrictions:
At the RDBMS level, you can decorate only two types of table constraints:

• unique attributes (keys)
• Requirements for subsets that reference the same or different table (foreign key)

Implementing all other types of table constraints (for example, constraints with multiple tuples or transition constraints) requires the development of code for the integrity of the procedures.
When the voucher is sold to a specific customer for a specific product, we will need to check the validity of excluded elements that can be executed by triggers at the database level or the business logic of your application.