Dropping ACID - Schema Design for e-commerce

Schema design presentation given at Symfony User Group Berlin

Transcript

1. Dropping ACID Schema Design for e-commerce
4. ERD Product
5. ERD Order
6. Product Entity /** @final */ class Product { private ?string $id = null; private string $title; private ?string $description = null; /** @var Collection<ProductVariant> */ private Collection $variants; /** @var Collection<ProductOption> */ private Collection $options; public function __construct(string $title) { $this->title = $title; $this->variants = new ArrayCollection(); $this->options = new ArrayCollection(); } }
7. Order Entity /** @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(); } }
8. Design the Database Schema
9. Database Normalisation • Unnormalised: “Just do whatever” • 1NF: “Domain of each attribute contains only atomic values” • … • 6NF: “Row contains the Primary Key, and at most one other attribute”
10. ERD -> Tables 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 (...);
11. Create a Product $product = new Product(); $product->setTitle('Symfony T-Shirt'); $product->addProductOption($color); $variant = new ProductVariant($product); $variant->setProductOptionValue($color, $black); $manager->persist($product); $manager->persist($variant); $manager->flush();
12. 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 (...);
13. ACID
15. ACID atomicity, consistency, isolation, durability
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. 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;
18. Finalise Payment START TRANSACTION; UPDATE sylius_payment SET state = 'completed' ...; UPDATE sylius_order SET payment_state = 'completed' ...; COMMIT;
19. You Don’t NEED Transactions
20. You Need a Different Database
22. You Need a Different Schema
23. Denormalise Your Schema
24. Cart { "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 }
25. 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" } ] } ] }
26. Order - Customer { "customer": { "id": 45, "name": "Andreas Braun", "shippingAddress": { "address": "<redacted>", "postCode": "<redacted>", "city": "<redacted>", "country": "Germany" }, "billingAddress": { "address": "<redacted>", "postCode": "<redacted>", "city": "<redacted>", "country": "Germany" } } }
27. Order - Payments { "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" } } ] }
28. Order - Shipments { "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" } ] } ] }
29. Relationship advice
30. References • A pointer to another entity • Referential integrity optional (MyISAM, MongoDB) • State of the art in an RDBMS
31. Embedded data • Not just a pointer to another entity, but all data • Update is done on the main entity • Not very useful if data is needed by itself
32. Choose Your Relationship • Reference data when it’s needed by itself • Embed data when it’s owned by another entity • Look at your queries
33. Create complex schemas
34. CLOB UPDATE sylius_order SET payments = 'a:1:{i:0;a:4:{s:6:"amount";i:2998;s:5:"state";...;}}}';
35. JSON/JSONb UPDATE sylius_order SET 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" } } ]';
36. Partial JSON Updates UPDATE sylius_order SET payments = JSON_SET( payments, '$[last].state', 'failed', ‘$[last].payment_method.details.error', '...' ) ;
37. Partial JSON Updates UPDATE sylius_order SET payments = JSON_ARRAY_APPEND( JSON_SET( payments, '$[last].state', 'failed', ‘$[last].payment_method.details.error', '...' ), CAST('{ "amount": 2998, "state": "pending", ... }' AS JSON) ) ;
38. Complex Types CREATE TYPE customer AS ( id INT, name VARCHAR(255), shippingAddress address, billingAddress address );
39. Update Complex Fields UPDATE sylius_order SET (customer).name = 'Andreas Braun';
40. Array Types CREATE TABLE sylius_order ( ..., payments payment[] );
41. Update Arrays UPDATE sylius_order SET (payments[0]).state = 'failed', payments = payments || ARRAY[CAST(ROW(123, ...) AS payment)] ;
42. Use MongoDB (yes, it’s ACID compliant)
43. Create Cart db.order.insert({ _id: 123, customer: 45, state: "cart", items: [ { variant: 1381, price: 1499, quantity: 2, units: [ { id: "1381-1", shipped: true }, { id: "1381-2", shipped: true } ] } ], items_total: 2998 });
44. Finish Checkout db.order.updateOne( { _id: 123}, { $set: { 'checkout_state': 'completed', 'state': 'awaiting_payment', 'customer': { 'name': 'Andreas Braun', ... } }, $push: { payments: { ... }, shipments: { ... } } } );
45. Atomic Updates db.order.updateOne( { _id: 123 }, { $set: { 'payments.$[failedPayment].state': 'failed', 'payments.$[failedPayment].details': { ... }, }, $push: { 'payments': { ... } } }, { arrayFilters: [ { 'failedPayment.id': { $eq: 1234 } } ] } );
46. Use Doctrine MongoDB ODM It’s like ORM, but for MongoDB
47. Use Doctrine MongoDB ODM It’s like ORM, but for MongoDB (and cooler)
48. Product in ODM /** * @ODM\Document * @final */ class Product { /** @ODM\Id */ private ?string $id = null; /** @ODM\Field(type="string") */ private string $title; /** @ODM\Field(type="string") */ private ?string $description = null; /** @ODM\ReferenceMany(targetDocument=ProductVariant::class) */ private Collection $variants; /** @ODM\ReferenceMany(targetDocument=ProductOption::class) */ private Collection $options; }
49. Order in 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; }
50. Embedded Documents /** @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\EmbedMany(targetDocument=OrderItemUnit::class) */ private Collection $units; }
51. Finish Checkout $order->setCheckoutState('completed'); $order->setState('awaiting_payment'); $order->setCustomer($customer); $order->addShipment($shipment); $order->addPayment($payment); $documentManager->persist($order); $documentManager->flush();
52. Finish Checkout db.order.updateOne( { _id: 123}, { $set: { 'checkout_state': 'completed', 'state': 'awaiting_payment', 'customer': { 'name': 'Andreas Braun', ... } }, $push: { payments: { ... }, shipments: { ... } } } );
53. More Relationship Advice
54. Hybrid Relationships • Keeps a reference to original entity • Stores some (or all) data in the relationship as well • Can be used in PostgreSQL or MongoDB
55. Using Complex Types CREATE TYPE customer AS ( id INT, name VARCHAR(255), shippingAddress address, billingAddress address );
56. Using DBRef in MongoDB { "id": 123, "customer": { "$ref": "customer", "$id": 45 } }
57. Using DBRef in MongoDB , "name": "Andreas Braun", "shippingAddress": { "address": "<redacted>", "postCode": "<redacted>", "city": "<redacted>", "country": "Germany" }, "billingAddress": { "address": "<redacted>", "postCode": "<redacted>", "city": "<redacted>", "country": "Germany" } { "id": 123, "customer": { "$ref": "customer", "$id": 45 } }
58. Think outside the box table
59. Think outside the box table
60. Thanks! @alcaeus github.com/alcaeus symfony-devs: @alcaeus