Dropping ACID - Schema Design for e-commerce

Schema Design presentation done at Symfony UG London

Transcript

1. Dropping ACID Schema Design for e-Commerce
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
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
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
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