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.

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. 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
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
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
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