Dropping ACID: Schema Design for E-Commerce

7ea0eec719c20e8d7880bfdb35f78b4e?s=47 alcaeus
December 04, 2020

Dropping ACID: Schema Design for E-Commerce

When designing a schema for e-commerce applications, we have to keep certain things in mind: data retention rules require duplicating large chunks of data, leading to complex schemas in any relational database. Modern database systems can help us solve this problem, but we seldom use their features when designing schemas. In this talk, we will take a look at some strategies on how to design more efficient schemas for certain database systems, and how to effectively drop ACID at work.

7ea0eec719c20e8d7880bfdb35f78b4e?s=128

alcaeus

December 04, 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. Databases

  5. Databases Choose Your Poison • SQL-based vs. “NoSQL” • Tabular

    storage vs. Rich models • Performance vs. Durability • Owned vs. Cloud Platform
  6. Databases The Trusted One • MySQL has been around for

    ages • LAMP, WAMP, MAMP packages make entry easy • Has caught up with other databases somewhat • Still owned by Oracle
  7. Databases The Title Contender • PostgreSQL is being used more

    and more • Features like Custom Types are huge • Allows for creating rich, nested data structures • Allows querying those structures • Somewhat held back by MySQL compatibility in Libraries
  8. Databases Webscale • MongoDB stores your data…and gives it back

    to you • Document model requires different approach to schema design • Lacks integration to important tools • Supporting it efficiently needs code changes
  9. Databases Finding Common Ground • All allow inserting simple data

    • All support inserting rich data • All are relational • All support clustering • All support transactions • Only one is Web Scale
  10. Relationship Advice

  11. Relationship Advice References • Pointer to another entity • Referential

    integrity optional (MyISAM, MongoDB) • RDBMS is built around this concept • MongoDB has rudimental support
  12. Relationship Advice The Case for References • References are supported

    in all databases • Implementations vary (e.g. DBRef in MongoDB) • Referential integrity ensures consistency • Reads across references possible in MongoDB, but different
  13. Relationship Advice Embedded Data • Not just a pointer to

    another entity, but all data • Persistence is managed by the owning entity • Not useful if data is needed by itself
  14. Relationship Advice Embed, don’t Reference • Embedding data groups entities

    together • Joining other entities no longer necessary • Limited support in MySQL (JSON types) • Interesting concepts in PostgreSQL (Custom Types) • MongoDB is built around this concept • This includes all flaws
  15. Relationship Advice Why not both? • Hybrid relationships combine references

    and embedded data • Stores reference to original entity, but duplicates data • May require additional writes to update such data • Reads can be optimised to avoid joins • Only useful if libraries play along (e.g. proxy objects)
  16. Model Your Data

  17. None
  18. None
  19. None
  20. Create Your Tables

  21. 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 (...);
  22. 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 );
  23. 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');
  24. Normalise Your Data

  25. 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”
  26. 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
  27. Cons of Normalisation • Data can seem disorganised • Queries

    may become more complex due to joins • Added complexity of writes to multiple tables
  28. Write to the Database Spot the Problem INSERT INTO sylius_product

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

  30. None
  31. ACID Atomicity Consistency Isolation Durability

  32. 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;
  33. You Don’t NEED Transactions

  34. You Need a Different Schema

  35. Stop Using References

  36. 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, ... );
  37. 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, ... );
  38. 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" }', ... );
  39. 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, ... );
  40. Look at Your Queries

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

    to exclude certain columns • Frequent subqueries • Frequent writes to multiple tables at once
  42. 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;
  43. 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;
  44. Look at Your Data

  45. 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 }
  46. 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" } ] } ] }
  47. 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" } } }
  48. 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": { ... } } ] }
  49. 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" } ] } ] }
  50. Use a Different Database

  51. None
  52. 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 });
  53. 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 } } ] } );
  54. Finish Checkout db.order.updateOne( { _id: 123 }, { $set: {

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

  56. 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(); } }
  57. 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; }
  58. 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; }
  59. 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; }
  60. 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();
  61. 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: { ... } } } );
  62. Update Cart Quantity With Doctrine MongoDB ODM $orderItem->updateQuantity(3); // OrderItemQuantityModifier

    creates/removes units $documentManager->persist($order); $documentManager->flush();
  63. 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
  64. Think Outside The Box Table

  65. Think Outside The Box Table

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