Slide 1

Slide 1 text

Dropping ACID Schema Design for e-Commerce

Slide 2

Slide 2 text

No content

Slide 3

Slide 3 text

Why e-Commerce? • Data retention requirements make for complex schemas • Lots of existing examples, good and bad (Magento, Sylius, Spryker, etc.) • It’s catchy!

Slide 4

Slide 4 text

Model Your Data

Slide 5

Slide 5 text

No content

Slide 6

Slide 6 text

No content

Slide 7

Slide 7 text

No content

Slide 8

Slide 8 text

Create Your Tables

Slide 9

Slide 9 text

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 (...);

Slide 10

Slide 10 text

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 );

Slide 11

Slide 11 text

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');

Slide 12

Slide 12 text

Normalise Your Data

Slide 13

Slide 13 text

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”

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

Cons of Normalisation • Data can seem disorganised • Queries may become more complex due to joins • Added complexity of writes to multiple tables

Slide 16

Slide 16 text

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 (...);

Slide 17

Slide 17 text

Have Some ACID

Slide 18

Slide 18 text

No content

Slide 19

Slide 19 text

ACID Atomicity Consistency Isolation Durability

Slide 20

Slide 20 text

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;

Slide 21

Slide 21 text

You Don’t NEED Transactions

Slide 22

Slide 22 text

You Need a Different Schema

Slide 23

Slide 23 text

Stop Using References

Slide 24

Slide 24 text

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, ... );

Slide 25

Slide 25 text

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, ... );

Slide 26

Slide 26 text

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": "", "postCode": "", "city": "", "country": "Germany" }', ... );

Slide 27

Slide 27 text

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('', ...)) AS address, ... );

Slide 28

Slide 28 text

Look at Your Queries

Slide 29

Slide 29 text

Potential Schema Issues • Frequent JOIN queries • Frequent projections to exclude certain columns • Frequent subqueries • Frequent writes to multiple tables at once

Slide 30

Slide 30 text

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;

Slide 31

Slide 31 text

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;

Slide 32

Slide 32 text

Look at Your Data

Slide 33

Slide 33 text

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 }

Slide 34

Slide 34 text

Make it an Order { "id": 123, "customer": { "id": 45, "name": "Andreas Braun", "shippingAddress": { "address": "", "postCode": "", "city": "", "country": "Germany" }, "billingAddress": { "address": "", "postCode": "", "city": "", "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": "", "postCode": "", "city": "", "country": "Germany" } } ], "shipments": [ { "units": ["1381-1", "1381-2"], "state": "registered", "shipment_method": { "type": "dhl", "trackingNumber": "12345678a" }, "shippingAddress": { "address": "", "postCode": "", "city": "", "country": "Germany" }, "history": [ { "date": "2019-11-21T14:23:04+01:00", "state": "registered" } ] } ] }

Slide 35

Slide 35 text

Make it an Order Customer Data { "customer": { "id": 45, "name": "Andreas Braun", "shippingAddress": { "address": "", "postCode": "", "city": "", "country": "Germany" }, "billingAddress": { "address": "", "postCode": "", "city": "", "country": "Germany" } } }

Slide 36

Slide 36 text

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": { ... } } ] }

Slide 37

Slide 37 text

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" } ] } ] }

Slide 38

Slide 38 text

Use a Different Database

Slide 39

Slide 39 text

No content

Slide 40

Slide 40 text

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 });

Slide 41

Slide 41 text

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 } } ] } );

Slide 42

Slide 42 text

Finish Checkout db.order.updateOne( { _id: 123 }, { $set: { 'checkout_state': 'completed', 'state': 'awaiting_payment', 'customer': { 'name': 'Andreas Braun', ... } }, $push: { payments: { ... }, shipments: { ... } } } );

Slide 43

Slide 43 text

Get a Head Start With Doctrine

Slide 44

Slide 44 text

Order Object /** @final */ class Order { private ?string $id = null; private string $state = 'cart'; private ?Customer $customer = null; /** @var Collection */ private Collection $items; private int $total = 0; public function __construct() { $this->items = new ArrayCollection(); } }

Slide 45

Slide 45 text

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; }

Slide 46

Slide 46 text

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; }

Slide 47

Slide 47 text

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; }

Slide 48

Slide 48 text

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();

Slide 49

Slide 49 text

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: { ... } } } );

Slide 50

Slide 50 text

Update Cart Quantity With Doctrine MongoDB ODM $orderItem->updateQuantity(3); // OrderItemQuantityModifier creates/removes units $documentManager->persist($order); $documentManager->flush();

Slide 51

Slide 51 text

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

Slide 52

Slide 52 text

Think Outside The Box Table

Slide 53

Slide 53 text

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