Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Dropping ACID - Schema Design for e-commerce

7ea0eec719c20e8d7880bfdb35f78b4e?s=47 alcaeus
January 29, 2020

Dropping ACID - Schema Design for e-commerce

Schema design presentation given at Symfony User Group Berlin

7ea0eec719c20e8d7880bfdb35f78b4e?s=128

alcaeus

January 29, 2020
Tweet

Transcript

  1. Dropping ACID Schema Design for e-commerce

  2. None
  3. None
  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

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

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