Share
Embed
Dropping ACID - Schema Design for e-commerce
Schema Design presentation done at Symfony UG London
alcaeus
April 29, 2020
Transcript
53 slides
-
Why e-Commerce? • Data retention requirements make for complex schemas
• Lots of existing examples, good and bad (Magento, Sylius, Spryker, etc.) • It’s catchy! -
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 (...); -
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 ); -
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'); -
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” -
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 -
Cons of Normalisation • Data can seem disorganised • Queries
may become more complex due to joins • Added complexity of writes to multiple tables -
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; -
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, ... ); -
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, ... ); -
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" }', ... ); -
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, ... ); -
Potential Schema Issues • Frequent JOIN queries • Frequent projections
to exclude certain columns • Frequent subqueries • Frequent writes to multiple tables at once -
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; -
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; -
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 } -
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" } ] } ] } -
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" } } } -
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": { ... } } ] } -
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" } ] } ] } -
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 }); -
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 } } ] } ); -
Finish Checkout db.order.updateOne( { _id: 123 }, { $set: {
'checkout_state': 'completed', 'state': 'awaiting_payment', 'customer': { 'name': 'Andreas Braun', ... } }, $push: { payments: { ... }, shipments: { ... } } } ); -
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(); } } -
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; } -
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; } -
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; } -
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(); -
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: { ... } } } ); -
Update Cart Quantity With Doctrine MongoDB ODM $orderItem->updateQuantity(3); // OrderItemQuantityModifier
creates/removes units $documentManager->persist($order); $documentManager->flush(); -
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