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

Two 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.

Lets now look at Store Configuration, running marketing campaings and tracking it

4. Store Configuration

This involves setting up a logo, banner, policies(privacy policy, terms of use, returns policy, contact us, about us , store location),setting up administration users and their roles and then creating products ,categories, setting up discounting mechanism, creating membership offers, setting up Payment methods, header info, footer info etc

STORE : This table stores basic information like name of store, description, about us text(HTML formatted if you like).

STORE_INFO : It stores textual information like contact_us text, privacy policy text, terms of use, returns policy , faq text. As you see this suffices for a store where these information items do not change often and there is no mechanism to track versions(which becomes important considering some of these are store policies and have legal repucursions). You can still achieve by creation child tables for each policy items with a version column to track differences and add more. That’s where the relational model suffers from too much of “normalization spaghetti”.

5. Marketing

Marketing is an inherent mechanism via which it aims to increase sales and reach out to more customers. A store would need 2 things :- a) Information on users who signed up for receiving news, offers, marketing materials b)mechanism to create a campaign and track as to how was the response received which determines success.

EMAIL_GENERATION : This table stores details of marketing email(s) sent to users. Assume you have a front-end which allows rich email construct development which can embed marketing links(with code) and it directs to your site with additional info.

MARKETING_TRACKING : This table stores campaign information in the most simplistic form like campaign name, code, date_start, date_end, orders (basically track orders which came via this campaign. Minor changes in Orders table to include campaign code), campaign link to be sent via email or to be sent to social media mediums which will trigger traffic.

This completes the data model design for a simplistic yet feature rich! You have store configuration, marketing, discount handling, order capture, order processing, shipment tracking, customer creation, customer membership, Login, admin functions, auditing.

Hope you enjoyed.