A simple yet feature rich eCommerce data model part 2

1. Discounting Continued and Product Reviews…

A bit of repetition on Membership discount carried over from part 1 here …

Membership Discounting

While the MEMBERSHIP has definitions, the CUSTOMER_MEMBERSHIP_DETAILS stores the customers who have availed the membership. The MEMBERSHIP_DISCOUNT tells whether the products have discount offerings in this membership OR it’s a discount on total value of order. The front-end can control it if admin selects products then discount_percent_on_total should be disabled. The MEMBERSHIP_AUDIT stores the auditing information like which admin user created the membership type and when.

Coupon Discounting

COUPON : Coupon table stores basic attributes like name, code(use a unique generator),type (Percentage/Flat amount), discount =value(either percentage or flat amount), date_start: start of applicability when used, date_end: when applicability ends, max_coupon_usage_count=Number of times coupon can be used by any customer(upon usage the counter increases and when it matches this value the coupon code needs to be disabled . A small table COUPON_COUNTER_USAGE can be used), max_usage_by_any_customer=Number of times a coupon can be used by a customer(repeated use by a customer . A CUSTOMER_COUPON_USAGE can be used for coupon usage counter).

COUPON_PRODUCT_ASSOCIATION: This is the key table which stores coupon , product association. Basically, this coupon can be used with which products and hence the discounts get applied for those products in the order. A coupon at product category level can also be designed e.g:- COUPON:= COU#1239RTR applicable for Dairy Products category(curd ,milk, ghee ,tofu)

2. Product Reviews

A simple table which gets updated by user interaction. A prime candidate for no-sql DB. An aggregation engine can run overnight to generate avg ratings and store in a PRODUCT_REVIEWS_FRONTEND table for easy query and display.

3. Reward Points

2 areas which gather reward points for customer :- a) Reward points marked for a product purchase b) Reward Points on total order value. The points allocated for a product purchase can be a field in the PRODUCT table, points for total order value can be stored in REWARD_POINTS table. The accumulated reward points can be stored for a customer in CUSTOMER_REWARD_POINTS table which contains rows per order placed with points earned with status column = REDEEMED / AVAILABLE supported with status_change date columns.

In part 3 I’ll write on Store configuration, running marketing campaigns, tracking it…