Schema Design for E-Commerce Replacing Transactions with MongoDB

No content

Schema for e-commerce

ERD

E-Commerce schemas

Checkout • Collect data (shipping, billing) • Make sure cart information is accurate (prices) • Check if stock is accurate • Finalise order

Finalise order • Data becomes immutable • Shipping data must remain same • Billing data must remain same • Product data must remain same

Let’s fill a cart • Create Order in “cart” state • Add OrderItem with product information and quantity • Add OrderItemUnit

Change the cart • Create or remove OrderItemUnit • Update quantity and totals in OrderItem • Update quantity and totals in Order • Recompute promotions and adjustments (e.g. shipping)

Work with carts START TRANSACTION; INSERT INTO sylius_order (id, customer, items_total) VALUES (1, 45, 2998); INSERT INTO sylius_order_item (id, order_id, variant, price, quantity) VALUES (1, 1, 1381, 1499, 2); INSERT INTO sylius_order_item_unit (id, order_item_id, shipped) VALUES ('1381-1', 1, false), ('1381-2', 1, false); COMMIT;

Increase quantity START TRANSACTION; INSERT INTO sylius_order_item_unit VALUES ('1381-3', 1, false); UPDATE sylius_order_item SET quantity = 3 WHERE id = 1; UPDATE sylius_order SET items_total = 4497; COMMIT;

Decrease quantity START TRANSACTION; DELETE FROM sylius_order_item_unit WHERE id = '1381-3'; UPDATE sylius_order_item SET quantity = quantity - 1 WHERE id = 1; UPDATE sylius_order SET items_total = items_total - 1499; COMMIT;

The Transaction is a LIE

You don’t NEED transactions

Denormalise your schema

Cart { "id": 123, "customer": 45, "state": "cart", "items": [ { "variant": 1381, "price": 1499, "quantity": 2, "units": [ { "id": "1381-1", "shipped": false }, { "id": "1381-2", "shipped": false } ] } ], "items_total": 2998 }

Order { "id": 123, "customer": { "id": 45, "name": "Andreas Braun", "shippingAddress": { "address": "", "postCode": "", "city": "", "country": "Germany" }, "billingAddress": { "address": "", "postCode": "", "city": "", "country": "Germany" } }, "state": "new", "items": [ { "variant": 1381, "price": 1499, "quantity": 2, "units": [ { "id": "1381-1", "shipped": true }, { "id": "1381-2", "shipped": true } ] } ], "items_total": 2998, "payments": [ { "amount": 2998, "state": "completed", "payment_method": { "type": "credit_card", "details": { "cardHolder": "Andreas Braun", "creditCard": "xxxxxxxxxxxx1234", "expiration": "09/24", "gateway": "easy-payment-service", "identifier": "afae87a0" } }, "billingAddress": { "address": "", "postCode": "", "city": "", "country": "Germany" } } ], "shipments": [ { "units": ["1381-1", "1381-2"], "state": "registered", "shipment_method": { "type": "dhl", "trackingNumber": "12345678a" }, "shippingAddress": { "address": "", "postCode": "", "city": "", "country": "Germany" }, "history": [ { "date": "2019-11-21T14:23:04+01:00", "state": "registered" } ] } ] }

Order - Customer { "customer": { "id": 45, "name": "Andreas Braun", "shippingAddress": { "address": "", "postCode": "", "city": "", "country": "Germany" }, "billingAddress": { "address": "", "postCode": "", "city": "", "country": "Germany" } } }

Order - Payments { "payments": [ { "amount": 2998, "state": "completed", "payment_method": { "type": "credit_card", "details": { "cardHolder": "Andreas Braun", "creditCard": "xxxxxxxxxxxx1234", "expiration": "09/24", "gateway": "easy-payment-service", "identifier": "afae87a0" } }, "billingAddress": { "address": "", "postCode": "", "city": "", "country": "Germany" } } ] }

Order - Shipments { "shipments": [ { "units": ["1381-1", "1381-2"], "state": "registered", "shipment_method": { "type": "dhl", "trackingNumber": "12345678a" }, "shippingAddress": { "address": "", "postCode": "", "city": "", "country": "Germany" }, "history": [ { "date": "2019-11-21T14:23:04+01:00", "state": "registered" } ] } ] }

Use MongoDB (yes, it’s ACID compliant)

Create cart db.order.insert({ id: 123, customer: 45, state: "cart", items: [ { variant: 1381, price: 1499, quantity: 2, units: [ { id: "1381-1", shipped: true }, { id: "1381-2", shipped: true } ] } ], items_total: 2998 });

Increase quantity db.order.updateOne( { id: 123 }, { $inc: { 'items.0.quantity': 1, 'items_total': 1499 }, $push: { 'items.0.units': { id: "1381-3", shipped: false } } } );

Decrease quantity db.order.updateOne( { id: 123 }, { $inc: { 'items.0.quantity': -1, 'items_total': -1499 }, $pull: { 'items.0.units': { id: "1381-3" } } } );

Use Doctrine MongoDB ODM It’s like ORM, but for MongoDB

What if I can’t?

JSON columns! CREATE TABLE sylius_order ( id INT PRIMARY KEY NOT NULL, state CHAR(20) NOT NULL DEFAULT 'cart', items_total INT NOT NULL DEFAULT 0, customer JSON, items JSON );

SQL with JSON INSERT INTO sylius_order (id, state, items_total, customer, items) VALUES ( 1, 'cart', 2998, '{ "id": 45 }', '[ { "variant": 1381, "price": 1499, "quantity": 2, "units": [ { "id": "1381-1", "shipped": false }, { "id": "1381-2", "shipped": false } ] } ]' );

Increase quantity UPDATE sylius_order SET items_total = 4497, items = JSON_ARRAY_APPEND( JSON_SET(items, '$[0].quantity', 3), '$[0].units', CAST('{ "id": "1381-3", "shipped": false }' AS JSON) ) ;

New cart { "id": 123, "customer": 45, "state": "cart", "items": [ { "variant": 1381, "price": 1499, "quantity": 3, "units": [ { "id": "1381-1", "shipped": false }, { "id": "1381-2", "shipped": false }, { "id": "1381-3", "shipped": false } ] } ], "items_total": 4497 }

Decrease quantity UPDATE sylius_order SET items_total = 2998, items = JSON_REMOVE( JSON_SET(items, '$[0].quantity', 2), '$[0].units[2]' ) ;

Cart { "id": 123, "customer": 45, "state": "cart", "items": [ { "variant": 1381, "price": 1499, "quantity": 2, "units": [ { "id": "1381-1", "shipped": false }, { "id": "1381-2", "shipped": false } ] } ], "items_total": 2998 }

Think outside the box table

Think outside the box table

Thanks! @alcaeus github.com/alcaeus symfony-devs: @alcaeus