iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
The safest way to design an e-commerce database is to model the store’s business entities and the reads your application must serve, then enforce their relationships with database constraints. For most stores, begin with a normalized SQL schema covering customers, addresses, products, variants, categories, carts, orders, order items, inventory, payments, shipments, and returns. Preserve purchase-time facts—such as price, tax, currency, and shipping address—on the order rather than reading them from changeable catalog records. Choose a NoSQL design only after listing access patterns in detail; a document or single-table model can scale well when those patterns are known, but it makes relationship-heavy queries and reporting less flexible.
What should an e-commerce database accomplish?
An online store database has to answer several different questions without allowing one workflow to corrupt another. It must support browsing and search, maintain a mutable cart, create an immutable-enough order record, reserve stock, accept payment callbacks, coordinate fulfillment, and provide trustworthy reports. Those needs pull in different directions: catalog data changes frequently, while an old order must continue to show exactly what the customer bought and paid for.
Start by writing the user journeys and the concrete reads behind them. Typical journeys include:
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Scan for outdated or missing drivers - takes under a minute3Clear out junk files and repair common Windows errors- Browse categories and product pages, including variant availability and current price.
- Search products by attributes, SKU, or category.
- Create a cart, change quantities, and calculate a current total.
- Check out, reserve inventory, authorize payment, and create an order.
- Allocate stock to a warehouse, create a shipment, and record delivery events.
- Issue a refund or return against specific order lines.
- Produce operational, financial, tax, and inventory reports.
Each read should be traceable to one or more tables and indexes. This exercise exposes missing relationships before you choose a database engine.
#1 Best Overall
- Read Before You Buy — No Video Output: These adapters support charging and USB 2.0 data transfer, but cannot transmit video signals. Except for standard USB webcams (which use USB data only), they are not compatible with HDMI/DisplayPort cables, video-capable USB-C hubs, or docking stations with video output.
- Convert USB-A Ports to USB-C: Designed to connect USB-C earphones, cables, flash drives, card readers, and other USB-C accessories to standard USB-A ports. Plug-and-play with no drivers or software required.
- Aluminum Alloy Housing: Built with a sturdy aluminum alloy shell that aids in heat dissipation and protects against daily wear and scratches. Designed to maintain a stable and secure connection.
- Compact & Travel-Friendly: The ultra-compact design allows the adapter to stay plugged into your device without blocking adjacent ports or adding bulk, reducing wear and tear on your original USB ports.
- 12-Month Warranty: Backed by a 12-month manufacturer warranty for peace of mind. Designed to meet strict quality control standards for reliable everyday performance.
How do I translate store concepts into tables?
A relational baseline separates facts that have different lifecycles. The following model covers the core entities without repeating the same fact in multiple places.
| Table | Role in the store | Important relationships |
|---|---|---|
| customers | Account identity, contact details, and customer-level preferences | One customer can have many addresses, carts, orders, and payments |
| addresses | Saved customer addresses or copies attached to a transaction | Belongs to a customer when saved; an order can retain billing and shipping copies |
| products | Stable catalog identity and descriptive content | Has product variants and can belong to many categories |
| product_variants | Sellable SKU, price, attributes, and other unit-level data | References a product; inventory is tracked per variant and location |
| categories | Category names and hierarchy | Connected to products through product_categories when membership is many-to-many |
| product_categories | Junction table for catalog membership | One row per product/category pairing |
| inventory | Current quantity for a variant at a warehouse or other location | References a product variant and warehouse; protected by concurrency rules |
| inventory_movements | Auditable record of receipts, reservations, releases, adjustments, and shipments | References inventory and the business event that caused the movement |
| carts | Mutable pre-order state | Belongs to a customer or an anonymous session and has cart items |
| cart_items | Variant and quantity currently in a cart | References a cart and product variant |
| orders | Order identity, customer, currency, totals, lifecycle status, and timestamps | Has order items, payment records, shipments, and return activity |
| order_items | Products purchased, quantities, and purchase-time monetary values | Junction between orders and variants, with historical snapshots |
| payments | Provider reference and authorization, capture, and refund state | Belongs to an order; has payment events and idempotency keys |
| payment_events | Append-only record of provider callbacks and state changes | References a payment and the provider event identifier |
| shipments | Warehouse allocation, carrier state, tracking, and delivery status | Belongs to an order and has shipment items and delivery events |
| returns_refunds | Returned quantities and money refunded for order lines | Links order items to refund or payment events |
Use a generated primary key for each entity and keep business identifiers such as SKU, order number, and provider reference in separate columns. A business identifier can have a unique constraint without being the physical key used by every foreign key.
How should products, variants, and categories be modeled?
Keep the product as the descriptive catalog object and the variant as the sellable unit. A shirt, for example, may be one product with size-and-color variants; each variant needs its own SKU, price, attributes, and inventory balance.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Products and variants
The products table can hold the title, description, brand-neutral merchandising fields, and a status such as active or archived. Product_variants should contain the SKU, variant attributes, sellable price, and any unit-specific identifiers. Do not put a repeating list of SKUs, prices, or sizes into one product row: one value per cell keeps the structure queryable and prevents update anomalies.
If a product has no meaningful variants, represent its single sellable unit as one product_variant row anyway. This gives carts, inventory, and order_items one consistent foreign key rather than two competing paths.
Categories and hierarchy
Store categories separately so a category name or parent can change once. If a product can appear in several categories, use product_categories as a junction table; the relationship is many-to-many because one product can be listed in multiple category paths and one category contains many products. A parent-category foreign key supports a tree, while a separate ordering column can control presentation without altering product data.
How do I model carts, orders, and order totals?
Carts are temporary and editable; orders are historical records created from a checkout attempt. Keep those lifecycles separate so removing an abandoned cart cannot remove evidence of a completed purchase.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchPC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Rank #2
- 5-in-1 USB-C Hub: Experience comprehensive connectivity featuring a Power Delivery input, two USB-A 2.0 ports, a USB-A 3.0 port, and an HDMI port. (Note: The USB-C power delivery input port is only for connecting an external wall charger to power your laptop and cannot power peripheral devices.)
- 90W Pass-Through Charging: Achieve optimal charging with 90W pass-through power to your laptop, supported by a total input of 100W, with the hub reserving 10W for operational efficiency. (Note: Wall charger not included.)
- Quick Data Transfers: Accelerate your productivity with rapid data transfers using a high-speed 5Gbps USB 3.0 port and two 480Mbps USB 2.0 ports.
- 4K HDMI Display: Enhance your visual experience with a hub capable of delivering 4K resolution at 30Hz in both mirror and extend modes. Please note that this hub is compatible with MacBook (macOS 12 and newer), Windows 10 and 11, ChromeOS, and laptops equipped with DP Alt Mode and Power Delivery. Note: This device is not compatible with Linux.
- What You Get: Anker USB-C Hub (5-in-1, 4K HDMI), welcome guide, 18-month warranty, and our friendly customer service.
Carts and cart items
A cart row should identify its owner (customer or anonymous session), currency, status, and timestamps. Cart_items stores one row per variant in the cart and a quantity. Enforce uniqueness on the pair of cart ID and variant ID so a quantity update cannot create duplicate lines. Recalculate a cart from current catalog and promotion rules, but do not treat the cart total as the final accounting record.
Orders and order items
An orders row should contain the customer reference when one exists, currency, subtotal, discounts, tax, shipping, grand total, lifecycle status, and creation and update timestamps. Order_items resolves the many-to-many relationship between orders and products: one order can contain several products, and one product can appear on many orders. Each line should include the purchased variant reference, quantity, unit price, and allocated discount and tax values.
Store the calculated order totals as amounts that can be reconciled to the lines. Add explicit fields for fulfillment, refunds, and other adjustments when your business rules require them; do not hide those amounts in an unstructured note. Use a decimal or integer-minor-unit representation appropriate to the currency, and store the currency code on the order and, where needed, on monetary records.
Which information must be snapshotted at checkout?
Anything that can change after purchase but is needed to explain the transaction belongs on the order or its child records as a snapshot. Catalog prices, product titles, tax rules, and customer addresses can all change; an old invoice must not silently change with them.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
- Price: order_items.unit_price records the price used for that purchase, independent of the current variant price.
- Quantity: keep the purchased quantity even if the variant is later discontinued.
- Currency and totals: record the currency, subtotal, tax, discount, shipping, and grand total that were calculated at checkout.
- Addresses: copy billing and shipping address fields onto the order or into order-specific address rows. A customer editing a saved address must not rewrite a delivered order.
- Tax and adjustments: retain the tax allocation, discount allocation, fulfillment charges, refunds, and manual adjustments needed to reconcile the final amount.
- Fulfillment details: shipment records should preserve the warehouse allocation, carrier state, tracking identifier, and delivery events associated with that order.
Keep the live foreign key to the product variant for navigation and reporting, but use the copied values for historical display and financial reconciliation.
How does normalization prevent duplicate and broken data?
Normalize the transactional model before optimizing it for reads. Normalization splits repeating groups and duplicated facts into related tables so an update happens in one place.
First normal form
First normal form requires one value per cell. A product row should not contain a comma-separated list of SKUs, and an order should not have columns such as product1, product2, and product3. Move those repeating values into product_variants and order_items.
Rank #3
- Sleek 7-in-1 USB-C Hub: Features an HDMI port, two USB-A 3.0 ports, and a USB-C data port, each providing 5Gbps transfer speeds. It also includes a USB-C PD input port for charging up to 100W and dual SD and TF card slots, all in a compact design.
- Flawless 4K@60Hz Video with HDMI: Delivers exceptional clarity and smoothness with its 4K@60Hz HDMI port, making it ideal for high-definition presentations and entertainment. (Note: Only the HDMI port supports video projection; the USB-C port is for data transfer only.)
- Double Up on Efficiency: The two USB-A 3.0 ports and a USB-C port support a fast 5Gbps data rate, significantly boosting your transfer speeds and improving productivity.
- Fast and Reliable 85W Charging: Offers high-capacity, speedy charging for laptops up to 85W, so you spend less time tethered to an outlet and more time being productive.
- What You Get: Anker USB-C Hub (7-in-1), welcome guide, 18-month warranty, and our friendly customer service.
Second normal form
Second normal form requires every non-key column to depend on the whole key. In a junction table whose key is (order_id, variant_id), a product description depends only on variant_id, not on the complete pairing, so it belongs in products or product_variants instead.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →Third normal form
Third normal form requires non-key columns to depend on the key and nothing but the key. For example, do not copy a customer’s current city into every unrelated customer record or derive a category name from a product row. Keep each fact with the entity that owns it, then reference that entity with a foreign key.
Deliberate denormalization is reasonable when measured read performance justifies it. Document the duplicated field, the authoritative source, and the process that refreshes it; otherwise a faster query can create conflicting totals or stale catalog information.
Which database constraints are essential?
Constraints turn business rules into executable checks rather than relying solely on application code. PostgreSQL describes a primary key as unique and not null, a foreign key as a requirement that referenced values exist, and a unique constraint as protection for one column or a combination of columns.
- Primary keys: give every table a stable identifier and prevent duplicate entity rows.
- Foreign keys: connect order_items to orders and variants, inventory to variants and locations, and payments and shipments to orders. Choose deletion behavior deliberately; historical orders generally should not disappear because a catalog row was retired.
- Unique constraints: protect SKUs, provider references, idempotency keys, and compound relationships such as (cart_id, variant_id).
- NOT NULL: require values that every valid row needs, such as an order currency or an order-item quantity.
- CHECK constraints: reject invalid ranges such as zero or negative quantities where the business disallows them, negative prices, or unsupported status transitions encoded as simple conditions.
Use a status history or event table when you need an audit trail of transitions rather than only the current status. Keep payment callbacks in payment_events and inventory changes in inventory_movements so an operator can explain how a current value was reached.
How should inventory and checkout handle concurrency?
Inventory is not just a number on a product page; it is a resource contested by simultaneous checkouts. Track quantity by product variant and warehouse in inventory, and write every reservation, release, receipt, adjustment, and shipment to inventory_movements.
During checkout, perform the stock check and reservation in a transaction or an equivalent atomic operation. A retry must not reserve the same units twice. Give a reservation an expiry or a clear release event, and make shipment allocation consume or convert the reservation rather than decrementing stock a second time.
Rank #4
- Dual Converters, Infinite Potential：Includes 2× USB C male to USB A female adapters and 2× USB A male to USB C female adapters. Perfect for a wide range of uses—tablets with Bluetooth keyboards, expand USB ports on macbook, and more. Two different converters for all your daily needs
- Next-Level 10Gbps & 3A Charging: No more slow 480Mbps, this usb to usb c adapter has a transfer speed of up to 10Gbps, allowing you to do more transferring in less time. This usb adapter fits both USB A and USB C charger, supporting up to 3A fast charging
- Upgraded Exquisite Craftsmanship: With an aluminum alloy housing and metal connector, the usbc to usb adapter is extremely durable and sturdy. Rigorously tested to withstand more than 10,000 times of plugging and unplugging, ensuring long-lasting performance
- Broad Compatible: The usb c to usb adapter widely supports all USB C/ USB A devices like laptops, tablets, cellphones, car chargers, and phone chargers. Such as compatible with MacBook Pro/Air 2023/2022, Thunderbolt 4/3 Devices,Apple MagSafe Watch 9/8/7/SE/Ultra, iPad Pro 2022/2021, Samsung Galaxy S23/S20/S10, and iPhone 17/16/15 Pro. Plug and play
- Please Note: To reach 10Gbps speed, keep the cable under 3.3 ft. For USB A Male to USB C adapters, try flipping the USB C connector. USB C Male to USB A adapters support bidirectional 10Gbps transfer within 3.3 ft
Payment callbacks and client retries need the same discipline. Store the provider’s event identifier and an idempotency key under a unique constraint; process a repeated callback as the same event instead of creating a second capture, refund, or order. Never store raw card numbers in this database. Keep provider tokens or references and the authorization, capture, and refund states needed to reconcile the order.
Should I use SQL or DynamoDB for an online shop?
Relational SQL is usually the simpler starting point when an application has many related entities, transactional checkout, and changing reporting questions. DynamoDB or another NoSQL design can be a strong fit when access patterns are known in advance, very high scale is required, and the team can maintain partition-key, sort-key, and index choices. The choice is not a claim that one engine is universally faster; it follows from the queries, consistency requirements, and operational skills of your system.
| Decision axis | Relational SQL | DynamoDB-style NoSQL |
|---|---|---|
| Access-pattern fit | Supports joins and ad-hoc queries when requirements evolve | Requires access patterns to be specified before key and index design |
| Transactions and consistency | Natural fit for multi-row checkout, stock, and payment workflows | Can provide transactions, but the data model must be designed around the operations being issued |
| Relationship complexity | Foreign keys and junction tables represent many-to-many relationships directly | Relationships are commonly embedded, duplicated, or retrieved through planned key patterns |
| Reporting flexibility | Rich joins and flexible filtering make new reports easier to prototype | Reports usually need purpose-built access paths or an export to an analytical system |
| Scaling behavior | Scale and partitioning decisions depend on the SQL platform and workload | Designed for predictable key-based access and distributed operation |
| Operational skill | Teams need schema, query, transaction, and index expertise | Teams need partition-key, sort-key, hot-key, and secondary-index expertise |
| Migration cost | Adding a new relationship can be straightforward but may require migrations | Changing an access pattern can require backfills, new indexes, or a new item shape |
| Platform integration | Maps naturally to conventional application frameworks and reporting tools | Works well when surrounding services already use key-value or document access |
How should a NoSQL design begin?
List the exact questions the system must answer before creating a table. An online-shop access list can include customers, products, warehouses, inventory, orders, invoices, payments, and shipments. For every question, identify the partition key, the sort-key order, the fields returned, and whether the read must be strongly consistent.
Single-table, multi-table, and aggregate designs are all valid alternatives. Choose between them according to which records are read together and how independently they scale; do not copy a relational schema into a document store and assume the result is optimized.
How does a Shopify-native app map these concepts?
When the application is built inside Shopify, relational ideas such as tables, columns, foreign keys, and constraints map to metafields and metaobjects. Keep Shopify’s canonical resources as the source of truth where the platform already owns the product, order, customer, or fulfillment record. Store only app-specific extensions in your own model, such as configuration, enrichment, workflow state, or an external-system reference.
Decide explicitly which system wins when a value can be edited in both places. A clear ownership rule prevents an app synchronization job from overwriting a merchant’s canonical data or creating two conflicting prices.
What is a practical implementation sequence?
Build the smallest correct transactional model first, then tune it with observed workload data. This sequence keeps business rules visible while leaving room for measured optimization.
Best Value
- 5-in-1 Connectivity: Equipped with a 4K HDMI port, a 5 Gbps USB-C data port, two 5 Gbps USB-A ports, and a USB C 100W PD-IN port. Note: The USB C 100W PD-IN port supports only charging and does not support data transfer devices such as headphones or speakers.
- Powerful Pass-Through Charging: Supports up to 85W pass-through charging so you can power up your laptop while you use the hub. Note: Pass-through charging requires a charger (not included). Note: To achieve full power for iPad, we recommend using a 45W wall charger.
- Transfer Files in Seconds: Move files to and from your laptop at speeds of up to 5 Gbps via the USB-C and USB-A data ports. Note: The USB C 5Gbps Data port does not support video output.
- HD Display: Connect to the HDMI port to stream or mirror content to an external monitor in resolutions of up to 4K@30Hz. Note: The USB-C ports do not support video output.
- What You Get: Anker 332 USB-C Hub (5-in-1), welcome guide, our worry-free 18-month warranty, and friendly customer service.
- Write user journeys and reads. Document browse, search, cart, checkout, stock reservation, payment authorization, fulfillment, shipment, refund, and reporting queries. Include filters, sort orders, expected result shape, and consistency needs.
- Draw the entity-relationship diagram. Mark ownership and cardinality. Whenever two entities are many-to-many, add a junction table such as product_categories or order_items.
- Normalize the transactional schema. Remove repeating groups and duplicated facts. Keep product descriptions, customer profiles, and current prices in their owning tables, while reserving order snapshots for historical values.
- Define keys and constraints. Add primary keys, foreign keys, unique combinations, required columns, valid-range checks, and deletion behavior. Decide which statuses can transition and record transitions when an audit trail is required.
- Choose historical snapshots. Decide which addresses, prices, taxes, discounts, currencies, fulfillment charges, and adjustments must be copied when an order is placed. Record the data needed to reproduce an invoice without consulting mutable catalog rows.
- Add indexes from measured queries. Index foreign keys and high-frequency predicates after observing the workload. Typical candidates include order lookup by customer and date, order-item lookup by order, SKU lookup, inventory by variant and warehouse, and payment lookup by provider reference; confirm each with query plans rather than indexing every column.
- Design concurrency and idempotency tests. Exercise two checkouts competing for the last units, a checkout retry, a duplicated payment callback, a delayed shipment event, and a refund repeated by the provider. Verify that quantities, totals, and payment states remain explainable.
- Review operations and compliance. Set retention, privacy, tax, currency, regional-compliance, backup, recovery-objective, and audit requirements with the responsible specialists. These policies determine what may be stored, for how long, and how quickly it must be restored.
How should I test the schema before launch?
Use fixtures that cover a product with multiple variants, a product in several categories, an anonymous cart converted to a customer order, split shipments, partial refunds, and a discontinued variant that still appears on an old order. Confirm that historical pages use snapshots and that current catalog edits do not rewrite past invoices.
Run constraint tests as deliberately invalid operations: duplicate SKU, duplicate cart line, orphaned order item, zero quantity, negative adjustment, unknown payment event, and deletion of a referenced record. The database should reject each operation or route it through an explicit archival workflow.
For reporting, reconcile order totals from order_items, tax, discount, shipping, refund, and adjustment fields. For inventory, reconcile the current quantity with the movement log and unresolved reservations. These checks reveal silent drift that a happy-path checkout test will miss.
What common design mistakes cause trouble later?
- One giant order table: columns such as product_1, product_2, and product_3 impose an arbitrary line limit and make totals unreliable. Use order_items.
- Reading old orders from the live catalog: a price or title edit then changes the customer’s historical record. Store line snapshots.
- Putting all inventory in products: warehouse-level quantities and movement history cannot be represented accurately. Track variant and location separately.
- Trusting application checks alone: concurrent requests can bypass a check made before insertion. Enforce uniqueness, foreign keys, ranges, and idempotency in the database.
- Indexing by intuition: unused indexes add write and maintenance cost. Add them from measured predicates and query plans.
- Choosing NoSQL before listing reads: an item shape that looks elegant can become awkward when a new report or relationship appears. Start with access patterns.
- Storing raw payment-card data: this creates unnecessary security and compliance exposure. Store provider references and payment states instead.
- Deleting referenced catalog rows: historical orders lose context. Archive or deactivate products and variants, and preserve the references required for records and refunds.
What is the recommended baseline?
For a general e-commerce app, begin with a normalized relational schema: customers and addresses; products, variants, and categories; carts; orders and order_items; inventory and movement history; payments and payment events; shipments; and returns or refunds. Add constraints before indexes, snapshots before reporting, and concurrency tests before launch. Reconsider NoSQL only after your access-pattern list, scale needs, relationship complexity, reporting demands, and team skills show that a key-based model is the better operational fit.
Frequently Asked Questions
How should a database support guest checkout?
Allow an order to exist without a permanent customer account, while storing the contact and billing or shipping snapshots needed to fulfill it. If the guest later registers, link the existing order to the new customer without changing its historical address or monetary fields.
What should happen when a product is discontinued?
Mark the product or variant inactive for new carts and catalog views instead of deleting it. Keep its identifiers and order references available so returns, refunds, invoices, and customer-service searches continue to work.
Where should tax calculation results be stored?
Store the resulting tax amount and allocation on the order and order_items, along with the jurisdiction or rule identifier your tax process uses. Keeping the result with the transaction lets you reconcile it even after a tax configuration changes.
Recommended Free Tools
How can I tell whether an index is helping?
Capture the real query predicates and inspect execution plans and latency over representative traffic. Retain an index when it improves an important read enough to justify its write and storage cost; remove redundant indexes after verifying dependent queries.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
