Schema Design for e-Commerce (unconference version)

Transcript

1. Schema Design for E-Commerce Replacing Transactions with MongoDB

2. None

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