Dropping ACID Schema Design for e-Commerce

No content

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

Databases

Databases Choose Your Poison • SQL-based vs. “NoSQL” • Tabular storage vs. Rich models • Performance vs. Durability • Owned vs. Cloud Platform

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

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

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

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

Relationship Advice

Relationship Advice References • Pointer to another entity • Referential integrity optional (MyISAM, MongoDB) • RDBMS is built around this concept • MongoDB has rudimental support

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

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

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

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)

Model Your Data

No content

No content

No content

Create Your Tables

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

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

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

Normalise Your Data

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”

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

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

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

Have Some ACID

No content

ACID Atomicity Consistency Isolation Durability

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;

You Don’t NEED Transactions

You Need a Different Schema

Stop Using References

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

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

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

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

Look at Your Queries

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

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;

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;

Look at Your Data

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 }

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

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

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

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

Use a Different Database

No content

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

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

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

Get a Head Start With Doctrine

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

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

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

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

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

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

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

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

Think Outside The Box Table

Think Outside The Box Table

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