Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Schema Design for e-Commerce (unconference vers...

alcaeus
November 22, 2019

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.

alcaeus

November 22, 2019
Tweet

More Decks by alcaeus

Other Decks in Programming

Transcript

  1. ERD

  2. Checkout • Collect data (shipping, billing) • Make sure cart

    information is accurate (prices) • Check if stock is accurate • Finalise order
  3. Finalise order • Data becomes immutable • Shipping data must

    remain same • Billing data must remain same • Product data must remain same
  4. Let’s fill a cart • Create Order in “cart” state

    • Add OrderItem with product information and quantity • Add OrderItemUnit
  5. 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)
  6. 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;
  7. 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;
  8. 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;
  9. 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 }
  10. 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" } ] } ] }
  11. 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" } } }
  12. 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" } } ] }
  13. 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" } ] } ] }
  14. 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 });
  15. Increase quantity db.order.updateOne( { id: 123 }, { $inc: {

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

    'items.0.quantity': -1, 'items_total': -1499 }, $pull: { 'items.0.units': { id: "1381-3" } } } );
  17. 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 );
  18. 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 } ] } ]' );
  19. 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) ) ;
  20. 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 }
  21. Decrease quantity UPDATE sylius_order SET items_total = 2998, items =

    JSON_REMOVE( JSON_SET(items, '$[0].quantity', 2), '$[0].units[2]' ) ;
  22. 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 }