Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Speaker Deck
PRO
Sign in
Sign up for free
Schema Design for e-Commerce (unconference version)
alcaeus
November 22, 2019
Programming
2
270
Schema Design for e-Commerce (unconference version)
In this short talk, we will create an efficient and sensible e-commerce schema in just 20 minutes.
alcaeus
November 22, 2019
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
Dropping ACID - Schema Design for e-commerce
alcaeus
1
340
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
動画合成アーキテクチャを実装してみて
satorunooshie
0
540
フロントエンドエンジニアが変える現場のモデリング意識/modeling-awareness-changed-by-front-end-engineers
uggds
32
13k
アジャイルで不確実性に向き合うための開発タスクの切り方
tanden
4
1.1k
WindowsコンテナDojo: 第4回 Red Hat OpenShift Localを使ってみよう
oniak3ibm
PRO
0
180
Pluggable Storage in PostgreSQL
sira
1
190
パラメタライズドテスト
ledsun
0
220
ゴーファーくんと辿るプログラミング言語の歴史/history-of-programming-languages-with-gopher
iwasiman
11
4.9k
ECサイトの脆弱性診断をいい感じにやりたい/OWASPKansaiNight_LT1_220727
owaspkansai
0
280
JetpackCompose 導入半年で感じた 改善点
spbaya0141
0
140
Better Angular Architectures: Architectures with Standalone Components @DWX2022
manfredsteyer
PRO
1
380
Babylon.jsで作ったsceneをレイトレーシングで映えさせる
turamy
1
210
ベストプラクティス・ドリフト
sssssssssssshhhhhhhhhh
1
210
Featured
See All Featured
Statistics for Hackers
jakevdp
782
210k
Designing for humans not robots
tammielis
241
24k
Faster Mobile Websites
deanohume
294
28k
Scaling GitHub
holman
451
140k
How To Stay Up To Date on Web Technology
chriscoyier
780
250k
Refactoring Trust on Your Teams (GOTO; Chicago 2020)
rmw
21
1.4k
Building a Scalable Design System with Sketch
lauravandoore
448
30k
Fontdeck: Realign not Redesign
paulrobertlloyd
73
4.1k
BBQ
matthewcrist
74
7.9k
What's new in Ruby 2.0
geeforr
336
30k
Embracing the Ebb and Flow
colly
73
3.4k
No one is an island. Learnings from fostering a developers community.
thoeni
9
1.3k
Transcript
Schema Design for E-Commerce Replacing Transactions with MongoDB
None
Schema for e-commerce
ERD
E-Commerce schemas
Checkout • Collect data (shipping, billing) • Make sure cart
information is accurate (prices) • Check if stock is accurate • Finalise order
Finalise order • Data becomes immutable • Shipping data must
remain same • Billing data must remain same • Product data must remain same
Let’s fill a cart • Create Order in “cart” state
• Add OrderItem with product information and quantity • Add OrderItemUnit
Change the cart • Create or remove OrderItemUnit • Update
quantity and totals in OrderItem • Update quantity and totals in Order • Recompute promotions and adjustments (e.g. shipping)
Work with carts START TRANSACTION; INSERT INTO sylius_order (id, customer,
items_total) VALUES (1, 45, 2998); INSERT INTO sylius_order_item (id, order_id, variant, price, quantity) VALUES (1, 1, 1381, 1499, 2); INSERT INTO sylius_order_item_unit (id, order_item_id, shipped) VALUES ('1381-1', 1, false), ('1381-2', 1, false); COMMIT;
Increase quantity START TRANSACTION; INSERT INTO sylius_order_item_unit VALUES ('1381-3', 1,
false); UPDATE sylius_order_item SET quantity = 3 WHERE id = 1; UPDATE sylius_order SET items_total = 4497; COMMIT;
Decrease quantity START TRANSACTION; DELETE FROM sylius_order_item_unit WHERE id =
'1381-3'; UPDATE sylius_order_item SET quantity = quantity - 1 WHERE id = 1; UPDATE sylius_order SET items_total = items_total - 1499; COMMIT;
The Transaction is a LIE
You don’t NEED transactions
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" } ] } ] }
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 });
Increase quantity db.order.updateOne( { id: 123 }, { $inc: {
'items.0.quantity': 1, 'items_total': 1499 }, $push: { 'items.0.units': { id: "1381-3", shipped: false } } } );
Decrease quantity db.order.updateOne( { id: 123 }, { $inc: {
'items.0.quantity': -1, 'items_total': -1499 }, $pull: { 'items.0.units': { id: "1381-3" } } } );
Use Doctrine MongoDB ODM It’s like ORM, but for MongoDB
What if I can’t?
JSON columns! CREATE TABLE sylius_order ( id INT PRIMARY KEY
NOT NULL, state CHAR(20) NOT NULL DEFAULT 'cart', items_total INT NOT NULL DEFAULT 0, customer JSON, items JSON );
SQL with JSON INSERT INTO sylius_order (id, state, items_total, customer,
items) VALUES ( 1, 'cart', 2998, '{ "id": 45 }', '[ { "variant": 1381, "price": 1499, "quantity": 2, "units": [ { "id": "1381-1", "shipped": false }, { "id": "1381-2", "shipped": false } ] } ]' );
Increase quantity UPDATE sylius_order SET items_total = 4497, items =
JSON_ARRAY_APPEND( JSON_SET(items, '$[0].quantity', 3), '$[0].units', CAST('{ "id": "1381-3", "shipped": false }' AS JSON) ) ;
New cart { "id": 123, "customer": 45, "state": "cart", "items":
[ { "variant": 1381, "price": 1499, "quantity": 3, "units": [ { "id": "1381-1", "shipped": false }, { "id": "1381-2", "shipped": false }, { "id": "1381-3", "shipped": false } ] } ], "items_total": 4497 }
Decrease quantity UPDATE sylius_order SET items_total = 2998, items =
JSON_REMOVE( JSON_SET(items, '$[0].quantity', 2), '$[0].units[2]' ) ;
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 }
Think outside the box table
Think outside the box table
Thanks! @alcaeus github.com/alcaeus symfony-devs: @alcaeus