Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Speaker Deck
PRO
Sign in
Sign up for free
Dropping ACID - Schema Design for e-commerce
alcaeus
January 29, 2020
Programming
1
340
Dropping ACID - Schema Design for e-commerce
Schema design presentation given at Symfony User Group Berlin
alcaeus
January 29, 2020
Tweet
Share
More Decks by alcaeus
See All by alcaeus
Dropping ACID: Schema Design for E-Commerce
alcaeus
3
450
Dropping ACID - Schema Design for e-commerce
alcaeus
1
120
Schema Design for e-Commerce (unconference version)
alcaeus
2
270
Denormalise Your Database - Schema Design for Modern Database Systems
alcaeus
2
380
Doctrine - more than an ORM (Symfony User Group Osnabrück edition)
alcaeus
0
69
Doctrine - more than an ORM
alcaeus
0
920
Other Decks in Programming
See All in Programming
Automating Gradle benchmarks at N26
ubiratansoares
PRO
1
140
Pluggable Storage in PostgreSQL
sira
1
190
それ全部エラーメッセージに書いてあるよ!〜独学でPHPプログラミングが上達するたった一つの方法〜
77web
1
150
atama plusの開発チームはどのように「不確実性」に向き合ってきたか〜2022夏版〜
atamaplus
3
610
Scaling Productivity- How we have improved our dev experience
sockeqwe
1
120
クラウド KMS の活用 / TOKYO BLOCKCHAIN TECH MEETUP 2022
odanado
PRO
0
180
Getting Started With Data Structures
adoranwodo
1
260
レビュー駆動学習のススメ_StaPy#83
soogie
0
300
Now in Android Overview
aosa4054
0
390
kintone × LINE Bot で餃子検定Botを作った話
naberina
0
310
Haskellでオブジェクト指向プログラミング
koheisakata
0
180
ECサイトの脆弱性診断をいい感じにやりたい/OWASPKansaiNight_LT1_220727
owaspkansai
0
280
Featured
See All Featured
Design and Strategy: How to Deal with People Who Don’t "Get" Design
morganepeng
107
16k
Put a Button on it: Removing Barriers to Going Fast.
kastner
56
2.3k
Six Lessons from altMBA
skipperchong
14
1.4k
Making the Leap to Tech Lead
cromwellryan
113
7.4k
The Web Native Designer (August 2011)
paulrobertlloyd
75
2k
JavaScript: Past, Present, and Future - NDC Porto 2020
reverentgeek
37
3.3k
The Pragmatic Product Professional
lauravandoore
19
3.1k
What's in a price? How to price your products and services
michaelherold
229
9.4k
Faster Mobile Websites
deanohume
294
28k
GitHub's CSS Performance
jonrohan
1020
420k
Gamification - CAS2011
davidbonilla
75
3.9k
Dealing with People You Can't Stand - Big Design 2015
cassininazir
351
21k
Transcript
Dropping ACID Schema Design for e-commerce
None
None
ERD Product
ERD Order
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(); } }
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(); } }
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
None
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
None
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": "<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" } ] } ] }
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" } } }
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" } } ] }
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" } ] } ] }
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": "<redacted>", "postCode": "<redacted>", "city": "<redacted>", "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": "<redacted>", "postCode": "<redacted>", "city": "<redacted>", "country": "Germany" }, "billingAddress": { "address": "<redacted>", "postCode": "<redacted>", "city": "<redacted>", "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