A simple yet feature rich eCommerce data model part 1
A plethora of open source eCommerce apps are available for anyone to either use them as-it-is or customize. Hardly the documentation suffices for anyone to customize or to fix any inherent problems. A simplified, customized store can be built which suits to specific needs. A smaller niche shop doesn’t need all the fanciness offered by hosted ones which although ease basic set-up but at a times provide too many knobby configurations confusing the owners.
Here’s an attempt for creating your own E commerce Data Model. Its based on relational concept, however it can be easily translated to a No SQL DB or a hybrid version.
This is part 1 of the series where a basic data model is being presented. It doesn’t contain right now coupon/voucher handling, customer loyalty program, combo offers, refund. These would come in part 2. Suitable for a small store.
Let us get started
- Shop User and Customer
USER table:- stores id, user name, password, cell number. Typically, the users will be categorized into INTERNAL (Shop Admin, Shop Order Fulfilment guys) and EXTERNAL (customers who would place an order). The password field is applicable to INTERNAL users and the EXTERNAL users would typically access the web store, add items to cart and when ready would Checkout. The checkout flow can bring OTP Authentication where-in details would get captured for new users and also authentication.
ROLES table :- stores roles that can be created by admin user. E.g:- ADMIN, OPERATIONS, CUSTOMER
USER_ROLE table :- Stores typical user and role association. The association will help in admin screens
INTERNAL_USER_LOGIN_AUDIT :- It would store auditing information of ADMIN and OPERATIONS user along with action performed by them .E.g:- action_performed = PRODUCT-<ID>-‘<product>’-created. Or action_performed = ‘ORDER-<id>-ASSEMBLED-‘
EXTERNAL_USER_LOGIN_AUDIT:- It would store auditing information of customers.
CUSTOMERS :- This will store information of external customer, either new record or update existing record.
2. Product Definition and Configuration
PRODUCT_CATEGORY : Defines category of product e.g:- Shoe , Vegetables, Fruits, Package foods etc
PRODUCT :- Defines a product with its name , path of image(local or cloud file system path), unit = Kg, gms, piece, packet
PRODUCT_PRICING :- Defines the base price of a product ,status=active/inactive, selling price = the price for sell.
PRODUCT_ATTRIBUTE:- Defines attributes of a product like :- length, width, brand name or special mention. This particularly gets displayed on web ui for a product. Can also be translated to a No SQL store.
3. Order Capture and Processing
ORDER :- This table captures the order placed by customer(s). It has top level information like customer_id, date when order was placed, generated order code e.g:- ORDER#87878888, notes entered by customer and overall status of the order. In the beginning it would be ORDER_PLACED.
ORDER_LINE_ITEM :- This table captures product selected by customers, quantity. It will also have columns like quantity_fulfilled = Is the required quantity packaged or less. And line_item_status = PACKAGED, AWAITING_PACKAGING.
E.g:- product = Rice Pack , quantity ordered =5 , quantity_fulfilled =2 (INTERNAL user with operations role would have this field editable).
PAYMENT_METHOD, PAYMENT_METHOD_PARAMETERS : These 2 tables store payment method meta data like :- CREDIT_CARD, CASH, PAYPAL etc. The parameter table stores various parameters required for those methods.
PAYMENT_DETAILS :- It stores details of payment done by customer for an order.
4. Order Shipping
SHIPPING :- This table stores shipping information. The SHIPPING_ADDRESS basically captures customer shipping address specified during checkout flow. The courier_tracker field captures courier information like tracking details, additional information. The date fields capture the date-time when that action was performed by operations user. Potentially another role like SHIPMENT can be created for people who would actually initiate shipment.
ORDER_TRACKING: This table gets updated whenever operations get actioned on an order like :- ORDER_PLACED ,<so and so date> then ORDER_ASSEMBLED, ORDER_PACKAGED, ORDER_SHIPPED, ORDER_DELIVERED. Typically a manual operation via admin screens , potential for automation.
5. ORDER RETURNS
Customers can return products from an order due to damage or incorrect quantity or supplied product not as ordered.
ORDER_RETURNS : This table will contain the order id for which customer has applied for a “return”. The ORDER_RETURN_DETAIL contains details of product returned with quantity, reason, note for shop owner, type :- REQUIRE_REPLACEMENT or RE_FUND or OTHERS.
Possibly the Operations folks would create a record in ORDER table with type = REPLACEMENT_ORDERED or REFUND_REQUESTED.
ORDER_RETURN_APPROVAL : This table contains records in operations queue for verifying “returns”. Post verification the operations folks can APPROVE/DIS-APPROVE which would trigger an email , mark the status of returns as APPROVED/DIS-APPROVED. It would also trigger a flow which would refund the money in case re-fund is requested or get into operations queue for replacement shipping. For same order_code multiple records would get created with different statuses.
6. DISCOUNTS
Discounts here are either :- a) Flat discount on products E.g:- base price is 60, discounted_price :55 . b)Membership plans of which customer is part of and then overall discount gets applied on top order. E.g- Club membership 1 month , overall discount of 2 %, Club membership 3 months, 6 months, Gold membership 6 months — flat discount 6% on overall order.
MEMBERSHIP : This table stores membership definitions with TYPE= Club -1 month, GOLD 3 months etc. discount_value=3, discount_unit=percentage, fee : fees the customer has to pay to avail the service.
PRODUCT_PRICING : This table stores product discount as defined and the value gets used while computing total.
An additional table could be thought of like ORDER_TOTAL_CALCULATION which would store amount calculated based on discount price (product discounted price + discount on total computed based on membership). Also it would store discounts applied and money saved which can be used for customer’s viewing.
CUSTOMER_MEMBERSHIP_DETAILS :- This table would contain membership details of customer. By default it would contain a record with status as NONE so as to not to calculate any discount.
Last … Rewards and loyalty point calculation and incorporation can get fairly complex unless you want to do it and if you are operating at a larger scale. It also does not contain shipping calculation. Possibly attributes of a product like weight, length, width, address pin code can be used to determine shipping charges by contacting API of shipping provider.
Part 2: A simple yet feature rich eCommerce data model part 2
