Schema Design for e-Commerce (unconference version)

In this short talk, we will create an efficient and sensible e-commerce schema in just 20 minutes.

Transcript

1. Schema Design for E-Commerce Replacing Transactions with MongoDB
3. Schema for e-commerce
4. ERD
5. E-Commerce schemas
6. Checkout • Collect data (shipping, billing) • Make sure cart information is accurate (prices) • Check if stock is accurate • Finalise order
7. Finalise order • Data becomes immutable • Shipping data must remain same • Billing data must remain same • Product data must remain same
8. Let’s fill a cart • Create Order in “cart” state • Add OrderItem with product information and quantity • Add OrderItemUnit
9. 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)
10. 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;
11. 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;
12. 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;
13. The Transaction is a LIE
14. You don’t NEED transactions
15. Denormalise your schema
16. 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 }
17. Order { "id": 123, "customer": { "id": 45, "name": "Andreas Braun", "shippingAddress": { "address": "<redacted>", "postCode": "<redacted>", "city": "<redacted>", "country": "Germany" }, "billingAddress": { "address": "<redacted>", "postCode": "<redacted>", "city": "<redacted>", "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": "<redacted>", "postCode": "<redacted>", "city": "<redacted>", "country": "Germany" } } ], "shipments": [ { "units": ["1381-1", "1381-2"], "state": "registered", "shipment_method": { "type": "dhl", "trackingNumber": "12345678a" }, "shippingAddress": { "address": "<redacted>", "postCode": "<redacted>", "city": "<redacted>", "country": "Germany" }, "history": [ { "date": "2019-11-21T14:23:04+01:00", "state": "registered" } ] } ] }
18. Order - Customer { "customer": { "id": 45, "name": "Andreas Braun", "shippingAddress": { "address": "<redacted>", "postCode": "<redacted>", "city": "<redacted>", "country": "Germany" }, "billingAddress": { "address": "<redacted>", "postCode": "<redacted>", "city": "<redacted>", "country": "Germany" } } }
19. 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": "<redacted>", "postCode": "<redacted>", "city": "<redacted>", "country": "Germany" } } ] }
20. Order - Shipments { "shipments": [ { "units": ["1381-1", "1381-2"], "state": "registered", "shipment_method": { "type": "dhl", "trackingNumber": "12345678a" }, "shippingAddress": { "address": "<redacted>", "postCode": "<redacted>", "city": "<redacted>", "country": "Germany" }, "history": [ { "date": "2019-11-21T14:23:04+01:00", "state": "registered" } ] } ] }
21. Use MongoDB (yes, it’s ACID compliant)
22. 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 });
23. Increase quantity db.order.updateOne( { id: 123 }, { $inc: { 'items.0.quantity': 1, 'items_total': 1499 }, $push: { 'items.0.units': { id: "1381-3", shipped: false } } } );
24. Decrease quantity db.order.updateOne( { id: 123 }, { $inc: { 'items.0.quantity': -1, 'items_total': -1499 }, $pull: { 'items.0.units': { id: "1381-3" } } } );
25. Use Doctrine MongoDB ODM It’s like ORM, but for MongoDB
26. What if I can’t?
27. 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 );
28. 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 } ] } ]' );
29. 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) ) ;
30. 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 }
31. Decrease quantity UPDATE sylius_order SET items_total = 2998, items = JSON_REMOVE( JSON_SET(items, '$[0].quantity', 2), '$[0].units[2]' ) ;
32. 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 }
33. Think outside the box table
34. Think outside the box table
35. Thanks! @alcaeus github.com/alcaeus symfony-devs: @alcaeus