E-commerce database design based on real business workflows

• We maintain all Order data in a single database providing an accurate view of Item/Product stock and Order fulfillment.
• From Order capture and validation to Order delivery, the production-ready E-commerce database schema efficiently supports the business operations. See Order Process and Status for more information.
• Our E-commerce database schema is offering real-time Order tracking and Order history archiving.
• The database design allows Store Employees and registered Customers to access Order Status information in real-time and to receive Order Status notifications.

• By supporting real-time inventory tracking, Data Model Pack's E-commerce database schema avoids the situations when the system could mistakenly indicate that an Item/Product is available when in fact it is out of stock.
• Our powerful E-commerce database design offers support to optimize stock management and to avoid the out of stock or over stock situations.
• We offer the best Inventory Management database solution by implementing Stock control indicators such as: Preferred Stock Quantity, Low Stock Quantity, Low Stock Warning and ensuring accurate tracking of Item Stock levels history: Initial Quantity, Stock Entry/Exit Quantity, Current Quantity.

• Our professionally-designed E-commerce database model allows deep insights into the Item Stock movements resulted from business operations such as: Stock Purchase, Stock Return, Sale Order, Sale Return, Inventory Count, Loss and Damage etc.
• An Item Stock movement can be a Stock Entry, when an Item Quantity is added to the Item Stock, or a Stock Exit, when an Item Quantity is decresead from the Item Stock. See Entity: STOCK_HISTORY for more information.
• Every Item Stock movement should have assigned an accounting Document to justify that Stock movement. See Subject Area: DOCUMENT for more information.
• For example, when a Customer requests to return a part or all the Order Items, the database ensures support for: recording the Sale Return request (see Entities: ORDER_HEADER, SALE_RETURN, SALE_RETURN_ITEM), updating the Item Stock (see Entities: STOCK_HISTORY, ITEM), processing Refund (see Entity: REFUND) and recoding all the required accounting documents (see Entity: DOCUMENT).

• Each Store has it's own data configuration such as: Name, Tax Code, main Currency, taxes to be applied, suppliers list etc. , and can use some shared data configuration such as: Item/Product Categories, Payment Methods, Sales Channels and more. See Subject Area: STORE for more information.
• The E-commerce application Administrator can create a new Store and add a Store Administrator. See Subject Area: EMPLOYEE AND ROLE for more information.

• The E-commerce database schema ensures support for structuring information about Customers from multiple Sales Channels.

• A Customer can have a Loyalty Card assigned that will entitle the Customer for promotions and marketing campaigns enrollments.
• The Registered Online Customers who placed an Order can have access to Orders Status and can assess the ordering experience through Order Review.
• Orders can be handed off to Customers through many delivery methods such as SHIPMENT and PICK-UP. Both delivery methods are handled in the same database model that makes the Order fulfillment tracking more easy.

• The system allows shipment tracking notifcations as the Shipment is integrated with Order Status history.
• Our E-commerce database model is tailored for real business workflows and supports flexible Order delivery scenarios.

• Customers can make the payments with many Payment Methods: CASH, CARD, DIGITAL ( Stripe, Paypal etc)
• Depending on Delivery method - SHIPMENT or PICK-UP, Orders can be paid IN ADVANCE or AT HANDOFF.
• Entity: PAYMENT_TERM implements the allowed combinations of Sales Channel, Delivery Method, Payment Method, Payment Time for an Order.

• The data model is flexible and the E-commerce application Administrator can easily add new Sales Channels, Delivery Types, Payment Methods. For example can add a record to Entity: PAYMENT_METHOD for a BANK_WIRED_TRANSFER payment method and then add a record to Entity: PAYMENT_TERM to reflect the new configuration.

• Data Model Pack's E-commerce database design supports Items/Products to be grouped by hierarchical Item Categories and listed in an Item Catalog.
• For every Item/Product, the database model ensures tracking of Supplier's detailed information, taxes applied by Supplier and taxes applied by Store.
• We created an integrated view of Suppliers and Item/Products so registered online Customers can get access to view all Items/Products sold by an individual Supplier/Company.

See Subject Area: ITEM for more information.

• Our E-commerce data model takes into consideration and manages all the Item Prices a Sales department owns.

• The data model also allows comprehensive pricing history tracking by recording the time interval within an Item Price is applicable.

• The E-Commerce Data Model Pack supports complex discounts/promotions plans.

• Our E-commerce data model supports flexible payment scenarios.
• An Invoice can have one or many payments applied and each payment can use a different payment method such as CASH, CARD or DIGITAL. The allowed payment methods are maintained in Entity: PAYMENT_TERM.
• For example for an Order placed and paid in a physical Store, the Invoice can have two payments applied if 30% of the total amount is paid with CASH and the rest of 70% of the total amount is paid with CARD.

• Each E-commerce application database has only one active Administrator.
• The Administrator has the reponsibility to populate the tables that contain List of Values: ROLE_TYPE, FUNCTION_TYPE, ROLE_FUNCTION, DOCUMENT_TYPE, UNIT_OF_MEASURE, CURRENCY, PAYMENT_METHOD etc which are general and can be used by all Stores.
• The Administrator has also the responsibility to create new Stores, and to add an Employee with Store Administrator Role to each new Store.
• Employees are application users who can access/manage the information stored in the E-commerce database accordingly to the Roles granted to them by application Administrator or by Stores Administrators.
• Roles help to control what application Functionalities/Functions can be accessed by an Employee.
• The application Administrator can create and assign to an Employee as many Roles as needed.

• Our E-commerce database schema supports user login sessions tracking, from the time the user account was created and was granted access to application functionalities, to the time the user access to application was revoked.
• We have created the possibility to track the operations on some critical database tables by adding columns (such as: Created_Time, Created_Employee_Login) to record the user sessions who initiated the operations and the timestamp when new records where added to these tables.