Share
Embed
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.
alcaeus
December 04, 2020
Transcript
66 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! -
Databases Choose Your Poison • SQL-based vs. “NoSQL” • Tabular
storage vs. Rich models • Performance vs. Durability • Owned vs. Cloud Platform -
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 -
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 -
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 -
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 -
Relationship Advice References • Pointer to another entity • Referential
integrity optional (MyISAM, MongoDB) • RDBMS is built around this concept • MongoDB has rudimental support -
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 -
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 -
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 -
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) -
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 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 (...); -
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