Dropping ACID - Schema Design for e-commerce

Transcript

1. Dropping ACID Schema Design for e-commerce

2. None
3. None

4. ERD Product

5. ERD Order

6. 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(); } }

7. 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(); } }

8. Design the Database Schema

9. 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”

10. 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 (...);

11. 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();

12. 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 (...);

13. ACID

14. None

15. ACID atomicity, consistency, isolation, durability

16. 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 (...);

17. 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;

18. Finalise Payment START TRANSACTION; UPDATE sylius_payment SET state = 'completed'

    ...; UPDATE sylius_order SET payment_state = 'completed' ...; COMMIT;

19. You Don’t NEED Transactions

20. You Need a Different Database

21. None

22. You Need a Different Schema

23. Denormalise Your Schema

24. 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 }

25. 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" } ] } ] }

26. 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" } } }

27. 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" } } ] }

28. 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" } ] } ] }

29. Relationship advice

30. References • A pointer to another entity • Referential integrity

    optional (MyISAM, MongoDB) • State of the art in an RDBMS

31. 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

32. Choose Your Relationship • Reference data when it’s needed by

    itself • Embed data when it’s owned by another entity • Look at your queries

33. Create complex schemas

34. CLOB UPDATE sylius_order SET payments = 'a:1:{i:0;a:4:{s:6:"amount";i:2998;s:5:"state";...;}}}';

35. 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" } } ]';

36. Partial JSON Updates UPDATE sylius_order SET payments = JSON_SET( payments,

    '$[last].state', 'failed', ‘$[last].payment_method.details.error', '...' ) ;

37. 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) ) ;

38. Complex Types CREATE TYPE customer AS ( id INT, name

    VARCHAR(255), shippingAddress address, billingAddress address );

39. Update Complex Fields UPDATE sylius_order SET (customer).name = 'Andreas Braun';

40. Array Types CREATE TABLE sylius_order ( ..., payments payment[] );

41. Update Arrays UPDATE sylius_order SET (payments[0]).state = 'failed', payments =

    payments || ARRAY[CAST(ROW(123, ...) AS payment)] ;

42. Use MongoDB (yes, it’s ACID compliant)

43. 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 });

44. Finish Checkout db.order.updateOne( { _id: 123}, { $set: { 'checkout_state':

    'completed', 'state': 'awaiting_payment', 'customer': { 'name': 'Andreas Braun', ... } }, $push: { payments: { ... }, shipments: { ... } } } );

45. Atomic Updates db.order.updateOne( { _id: 123 }, { $set: {

    'payments.$[failedPayment].state': 'failed', 'payments.$[failedPayment].details': { ... }, }, $push: { 'payments': { ... } } }, { arrayFilters: [ { 'failedPayment.id': { $eq: 1234 } } ] } );

46. Use Doctrine MongoDB ODM It’s like ORM, but for MongoDB

47. Use Doctrine MongoDB ODM It’s like ORM, but for MongoDB

    (and cooler)

48. 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; }

49. 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; }

50. 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; }

51. Finish Checkout $order->setCheckoutState('completed'); $order->setState('awaiting_payment'); $order->setCustomer($customer); $order->addShipment($shipment); $order->addPayment($payment); $documentManager->persist($order); $documentManager->flush();

52. Finish Checkout db.order.updateOne( { _id: 123}, { $set: { 'checkout_state':

    'completed', 'state': 'awaiting_payment', 'customer': { 'name': 'Andreas Braun', ... } }, $push: { payments: { ... }, shipments: { ... } } } );

53. More Relationship Advice

54. 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

55. Using Complex Types CREATE TYPE customer AS ( id INT,

    name VARCHAR(255), shippingAddress address, billingAddress address );

56. Using DBRef in MongoDB { "id": 123, "customer": { "$ref":

    "customer", "$id": 45 } }

57. 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 } }

58. Think outside the box table

59. Think outside the box table

60. Thanks! @alcaeus github.com/alcaeus symfony-devs: @alcaeus