Share
Embed
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
Transcript
35 slides
-
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; -
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" } ] } ] } -
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" } } } ); -
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 }