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

Dropping ACID - Schema Design for e-commerce

7ea0eec719c20e8d7880bfdb35f78b4e?s=47 alcaeus
April 29, 2020

Dropping ACID - Schema Design for e-commerce

Schema Design presentation done at Symfony UG London

7ea0eec719c20e8d7880bfdb35f78b4e?s=128

alcaeus

April 29, 2020
Tweet

Transcript

  1. Dropping ACID Schema Design for e-Commerce

  2. None
  3. Why e-Commerce? • Data retention requirements make for complex schemas

    • Lots of existing examples, good and bad (Magento, Sylius, Spryker, etc.) • It’s catchy!
  4. Model Your Data

  5. None
  6. None
  7. None
  8. Create Your Tables

  9. A Simple Schema CREATE TABLE sylius_product_option (...); CREATE TABLE sylius_product_option_value

    (...); CREATE TABLE sylius_product (...); CREATE TABLE sylius_product_options (...); CREATE TABLE sylius_product_variant (...); CREATE TABLE sylius_product_variant_option_value (...); CREATE TABLE sylius_order (...); CREATE TABLE sylius_order_item (...); CREATE TABLE sylius_order_item_unit (...);
  10. Normalise a Little More? CREATE TABLE product ( id INT

    PRIMARY KEY, title VARCHAR(255) NOT NULL ); CREATE TABLE product_description ( id INT PRIMARY KEY, description TEXT NOT NULL ); CREATE TABLE product_option ( id INT PRIMARY KEY, name VARCHAR(255) NOT NULL ); CREATE TABLE product_options ( product_id INT NOT NULL, product_option_id INT NOT NULL );
  11. Normalise a Little Less? CREATE TABLE product_option ( id INT

    PRIMARY KEY, name VARCHAR(255) NOT NULL, ... ); CREATE TABLE product ( id INT PRIMARY KEY, title VARCHAR(255) NOT NULL, ..., options TEXT NOT NULL ); INSERT INTO product VALUES (1, 'Shoe', ..., '1, 2, 3');
  12. Normalise Your Data

  13. Normal forms • Unnormalised data: “Just do whatever” • 1NF:

    “Domain of each attribute contains only atomic values” • 3NF: “No non-transitive dependencies” • … • 6NF: “Row contains the Primary Key, and at most one other attribute”
  14. Pros of Normalisation • Atomic columns reduce complexity • Reads

    can be more efficient (reading less data is faster) • Atomic, non-conflicting updates are facilitated • Data can seem more organised
  15. Cons of Normalisation • Data can seem disorganised • Queries

    may become more complex due to joins • Added complexity of writes to multiple tables
  16. Write to the Database INSERT INTO sylius_product VALUES (...); INSERT

    INTO sylius_product_options VALUES (...); INSERT INTO sylius_product_variant VALUES (...); INSERT INTO sylius_product_variant_option_value (...);
  17. Have Some ACID

  18. None
  19. ACID Atomicity Consistency Isolation Durability

  20. Write to the Database INSERT INTO sylius_product VALUES (...); INSERT

    INTO sylius_product_options VALUES (...); INSERT INTO sylius_product_variant VALUES (...); INSERT INTO sylius_product_variant_option_value (...); START TRANSACTION; COMMIT;
  21. You Don’t NEED Transactions

  22. You Need a Different Schema

  23. Stop Using References

  24. Address Data CREATE TABLE customer ( id INT PRIMARY KEY,

    ..., ); CREATE TABLE customer_address ( id INT PRIMARY KEY, customer_id INT NOT NULL, address TEXT NOT NULL, city TEXT NOT NULL, ... ); CREATE TABLE order ( id INT PRIMARY KEY, shipping_address INT NOT NULL, billing_address INT NOT NULL, ... );
  25. Address Data Using Doctrine Embeddables CREATE TABLE order ( id

    INT PRIMARY KEY, shipping_address TEXT NOT NULL, shipping_address_city TEXT NOT NULL, ..., billing_address TEXT NOT NULL, billing_address_city TEXT NOT NULL, ... );
  26. Address Data Using JSON/JSONb CREATE TABLE order ( id INT

    PRIMARY KEY, shipping_address JSON NOT NULL, billing_address JSON NOT NULL, ... ); INSERT INTO order VALUES ( 1, '{ "address": "<redacted>", "postCode": "<redacted>", "city": "<redacted>", "country": "Germany" }', ... );
  27. Address Data Using Custom Types CREATE TYPE address AS (

    id INT, address TEXT, city TEXT, ... ); CREATE TABLE order ( id INT PRIMARY KEY, shipping_address address NOT NULL, billing_address address NOT NULL, ... ); INSERT INTO order VALUES ( 1, CAST(ROW('<redacted>', ...)) AS address, ... );
  28. Look at Your Queries

  29. Potential Schema Issues • Frequent JOIN queries • Frequent projections

    to exclude certain columns • Frequent subqueries • Frequent writes to multiple tables at once
  30. Update Cart Amount START TRANSACTION; INSERT INTO sylius_order_item_unit VALUES (...);

    UPDATE sylius_order_item SET quantity = 3, total = 1998 WHERE ...; UPDATE sylius_order SET items_total = 1998 WHERE ...; COMMIT;
  31. Update Cart Amount A Little More Isolation START TRANSACTION; INSERT

    INTO sylius_order_item_unit VALUES (...); UPDATE sylius_order_item SET quantity = quantity + 1, total = total + 999 WHERE ...; UPDATE sylius_order SET items_total = items_total + 999 WHERE ...; COMMIT;
  32. Look at Your Data

  33. Cart Data { "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 }
  34. Make it an 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" } ] } ] }
  35. Make it an Order Customer Data { "customer": { "id":

    45, "name": "Andreas Braun", "shippingAddress": { "address": "<redacted>", "postCode": "<redacted>", "city": "<redacted>", "country": "Germany" }, "billingAddress": { "address": "<redacted>", "postCode": "<redacted>", "city": "<redacted>", "country": "Germany" } } }
  36. Make it an Order Payment Data { "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": { ... } } ] }
  37. Make it an Order Shipment Data { "shipments": [ {

    "units": ["1381-1", "1381-2"], "state": "registered", "shipment_method": { "type": "dhl", "trackingNumber": "12345678a" }, "shippingAddress": { ..., }, "history": [ { "date": "2019-11-21T14:23:04+01:00", "state": "registered" } ] } ] }
  38. Use a Different Database

  39. None
  40. Shopping Cart Insert Initial Cart document db.order.insert({ _id: 123, customer:

    45, state: "cart", items: [ { variant: 1381, price: 1499, total: 2998, quantity: 2, units: [ { id: "1381-1", shipped: true }, { id: "1381-2", shipped: true } ] } ], items_total: 2998 });
  41. Update Cart Quantity Update Quantity db.order.updateOne( { _id: 123 },

    { $inc: { 'items_total': 1499, 'items.$[matchingItem].quantity': 1, 'items.$[matchingItem].total': 1499 }, $push: { 'items.$[matchingItem].units': { id: “1381-3", shipped: false } } }, { arrayFilters: [ { 'matchingItem.variant': { $eq: 1381 } } ] } );
  42. Finish Checkout db.order.updateOne( { _id: 123 }, { $set: {

    'checkout_state': 'completed', 'state': 'awaiting_payment', 'customer': { 'name': 'Andreas Braun', ... } }, $push: { payments: { ... }, shipments: { ... } } } );
  43. Get a Head Start With Doctrine

  44. Order Object /** @final */ class Order { private ?string

    $id = null; private string $state = 'cart'; private ?Customer $customer = null; /** @var Collection<OrderItem> */ private Collection $items; private int $total = 0; public function __construct() { $this->items = new ArrayCollection(); } }
  45. Order Entity With Doctrine ORM /** * @ORM\Entity * @final

    */ class Order { /** @ORM\Id */ private ?string $id = null; /** @ORM\Column(type="string") */ private string $state = 'cart'; /** @ORM\ManyToOne(targetDocument=Customer::class) */ private ?Customer $customer = null; /** @ORM\OneToMany(targetDocument=OrderItem::class) */ private Collection $items; /** @ORM\Column(type="int") */ private int $total = 0; }
  46. Order Document With Doctrine MongoDB ODM /** * @ODM\Document *

    @final */ class Order { /** @ODM\Id */ private ?string $id = null; /** @ODM\Field(type="string") */ private string $state = 'cart'; /** @ODM\EmbedOne(targetDocument=Customer::class) */ private ?Customer $customer = null; /** @ODM\EmbedMany(targetDocument=OrderItem::class) */ private Collection $items; /** @ODM\Field(type="int", strategy="increment") */ private int $total = 0; }
  47. Embedded Documents With Doctrine MongoDB ODM /** @ODM\EmbeddedDocument */ final

    class OrderItem { /** @ODM\ReferenceOne(targetDocument=ProductVariant::class) */ private ProductVariant $variant; /** @ODM\Field(type="int") */ private int $price; /** @ODM\Field(type="int", strategy="increment") */ private int $quantity; /** @ODM\Field(type="int", strategy="increment") */ private int $total; /** @ODM\EmbedMany(targetDocument=OrderItemUnit::class) */ private Collection $units; }
  48. Finish Checkout With Doctrine MongoDB ODM $order->setCheckoutState('completed'); $order->setState('awaiting_payment'); $order->setCustomer($customer); $order->addShipment($shipment);

    $order->addPayment($payment); $documentManager->persist($order); $documentManager->flush();
  49. Finish Checkout Query from Doctrine MongoDB ODM db.order.updateOne( { _id:

    123 }, { $set: { 'checkout_state': 'completed', 'state': 'awaiting_payment', 'customer': { 'name': 'Andreas Braun', ... } }, $push: { payments: { ... }, shipments: { ... } } } );
  50. Update Cart Quantity With Doctrine MongoDB ODM $orderItem->updateQuantity(3); // OrderItemQuantityModifier

    creates/removes units $documentManager->persist($order); $documentManager->flush();
  51. Update Cart Quantity Query from Doctrine MongoDB ODM db.order.updateOne( {

    _id: 123 }, { $inc: { 'items_total': 999, 'items.$[matchingItem].quantity': 1, 'items.$[matchingItem].total': 999 }, $push: { 'items.$[matchingItem].units': { id: “1381-3", shipped: false } } }, { arrayFilters: [ { 'matchingItem.variant': { $eq: 1381 } } ] } ); NOT YET
  52. Think Outside The Box Table

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