Dropping ACID Schema Design for e-commerce

No content

No content

ERD Product

ERD Order

Product Entity /** @final */ class Product { private ?string $id = null; private string $title; private ?string $description = null; /** @var Collection */ private Collection $variants; /** @var Collection */ private Collection $options; public function __construct(string $title) { $this->title = $title; $this->variants = new ArrayCollection(); $this->options = new ArrayCollection(); } }

Order Entity /** @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(); } }

Design the Database Schema

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”

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

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

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

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

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;

Finalise Payment START TRANSACTION; UPDATE sylius_payment SET state = 'completed' ...; UPDATE sylius_order SET payment_state = 'completed' ...; COMMIT;

You Don’t NEED Transactions

You Need a Different Database

No content

You Need a Different Schema

Denormalise Your Schema

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 }

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

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

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": "", "postCode": "", "city": "", "country": "Germany" } } ] }

Order - Shipments { "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" } ] } ] }

Relationship advice

References • A pointer to another entity • Referential integrity optional (MyISAM, MongoDB) • State of the art in an RDBMS

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

Choose Your Relationship • Reference data when it’s needed by itself • Embed data when it’s owned by another entity • Look at your queries

Create complex schemas

CLOB UPDATE sylius_order SET payments = 'a:1:{i:0;a:4:{s:6:"amount";i:2998;s:5:"state";...;}}}';

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": "", "postCode": "", "city": "", "country": "Germany" } } ]';

Partial JSON Updates UPDATE sylius_order SET payments = JSON_SET( payments, '$[last].state', 'failed', ‘$[last].payment_method.details.error', '...' ) ;

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

Complex Types CREATE TYPE customer AS ( id INT, name VARCHAR(255), shippingAddress address, billingAddress address );

Update Complex Fields UPDATE sylius_order SET (customer).name = 'Andreas Braun';

Array Types CREATE TABLE sylius_order ( ..., payments payment[] );

Update Arrays UPDATE sylius_order SET (payments[0]).state = 'failed', payments = payments || ARRAY[CAST(ROW(123, ...) AS payment)] ;

Use MongoDB (yes, it’s ACID compliant)

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

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

Atomic Updates db.order.updateOne( { _id: 123 }, { $set: { 'payments.$[failedPayment].state': 'failed', 'payments.$[failedPayment].details': { ... }, }, $push: { 'payments': { ... } } }, { arrayFilters: [ { 'failedPayment.id': { $eq: 1234 } } ] } );

Use Doctrine MongoDB ODM It’s like ORM, but for MongoDB

Use Doctrine MongoDB ODM It’s like ORM, but for MongoDB (and cooler)

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

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

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

Finish Checkout $order->setCheckoutState('completed'); $order->setState('awaiting_payment'); $order->setCustomer($customer); $order->addShipment($shipment); $order->addPayment($payment); $documentManager->persist($order); $documentManager->flush();

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

More Relationship Advice

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

Using Complex Types CREATE TYPE customer AS ( id INT, name VARCHAR(255), shippingAddress address, billingAddress address );

Using DBRef in MongoDB { "id": 123, "customer": { "$ref": "customer", "$id": 45 } }

Using DBRef in MongoDB , "name": "Andreas Braun", "shippingAddress": { "address": "", "postCode": "", "city": "", "country": "Germany" }, "billingAddress": { "address": "", "postCode": "", "city": "", "country": "Germany" } { "id": 123, "customer": { "$ref": "customer", "$id": 45 } }

Think outside the box table

Think outside the box table

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