5 ms·

Building a scalable e-commerce data model

129 points by robric 6y ago

jamestimmins 6y ago
I really enjoy this type of article that shares a data model for a common type of project. I'd love to see more examples for different common project types.
- digaozao 6y ago
  Yes. I wish there was more of that. Sometimes I get some initial ideas of data models from here: http://www.databaseanswers.org/data_models/index.htm http://www.databaseanswers.org/data_models/index.htm
  - robric 6y ago
    Here are some data models for specific e-commerce use cases from that site: http://www.databaseanswers.org/data_models/top_ten_data_models_e_commerce.htm http://www.databaseanswers.org/data_models/top_ten_data_mode.... Thanks for sharing this.
rafaelturk 6y ago
If I ever venture into creating my own eCommerce platform I'd probably use NoSQL
- tekstar 6y ago
  You're going to want to run analytics, and to do that you need comparable data. Legacy data was a huge problem for the e-commerce giant I used to work at, even in The SQL database with a schema. NoSQL would be a silly non starter for any serious data modelling effort after a couple years of migrations outages, and code changes.
- dragonsh 6y ago
  eCommerce needs a hybrid data model. There are inherent relationships between various parts of eCommerce entities and it becomes very hard if just using noSQL with all the integrity constraints defined in application. This is one of the reason many companies moved back to relational model after dabbling with noSQL. I believe the best way to model it is to use something like PostgreSQL with jsonb docs for some type of data. If you are interested in noSQL database may be give a try to reactioncommerce (acquired recently), you will begin to hit limitations soon, given integrity constraint and ACID compliance becomes very important for some type of eCommerce data e.g. changing prices and inventory in real time which are consistent and correct. It becomes extremely hard and complex to manage it in application code, which comes free with relational database model.
- forgingahead 6y ago
  If I may share some advice - don't bother. As another commenter pointed out, relationships are inherent in a lot of the data you'll store for your platform, and for those elements that you'd like to keep the full data hash, just use jsonb in Postgres. Once you're at several million in revenue, then you can think about splitting off some parts of your data into document storage solutions for certain efficiency reasons, but that is by no means necessary. This is a great video on the topic: https://www.youtube.com/watch?v=HaEPXoXVf2k https://www.youtube.com/watch?v=HaEPXoXVf2k
- deleted 6y ago
  [deleted]
stevesimmons 6y ago
This is great, and worth comparing with, for instance, the Magento data models [1] (from Anna Volkl). Echoing the comment from rafaelturk about NoSQL, I'd love to see more info on how various industry verticals use DynamoDB as their primary store. [1] https://anna.voelkl.at/wp-content/uploads/2016/12/ce2.1.3.png https://anna.voelkl.at/wp-content/uploads/2016/12/ce2.1.3.pn...
- robric 6y ago
  That data model is legendary.
huy 6y ago
For those who're curious, the tool to build the database diagrams is dbdiagram.io
tmikaeld 6y ago
While Fabric is certainly cool and I'd love to dig into it, not having any inclination of the pricing makes it impossible to know which client-base they are after. I'm guessing those that have grown out of Magento/WooCommerce or even Shopify and Ecwid. Which means enterprise pricing, ie. custom quotes.
- robric 6y ago
  You're right on the money. We are for businesses that have outgrown those platforms. We don't surface pricing now. We need to keep our pricing flexible since we don't force businesses to purchase our entire platform like monoliths such as Salesforce and Oracle Commerce Cloud do. Instead of purchasing Fabric's entire headless commerce platform/product package, many businesses purchase individual or a couple products to start. They integrate these into their existing systems, then purchase more products (SaaS) down the road and integrate those. This increases time to value and removes the need for a total replatform. For instance, a multi-billion dollar company just purchased our PIM (https://fabric.inc/pim https://fabric.inc/pim) to start. They also want us to help with system integration. The needs and use cases are just too varied to offer blanket pricing at this point.
pbourke 6y ago
The OMS is a pretty crucial part of an e-commerce system. I’d outsource a great many other things before that.
revskill 6y ago
Where's the inventory management ? If i make a sale order, where to substract from inventory count ?
- rglullis 6y ago
  > Where's the inventory management? Salesforce /me ducks
- robric 6y ago
  This article did not explore that. However, order management systems by headless commerce platforms (not Salesforce) do a good job of handling this in an intuitive way.
lovetocode 6y ago
This is clearly an advertisement.
- pc86 6y ago
  Easily 50% of the articles on HN are advertisements. If you remove news articles and personal blogs that jumps to about 98%.
  - robric 6y ago
    We like to create content about alternative solutions and then plug our e-commerce SaaS. This is referred to as content marketing. As for the blogs and news articles you speak of, these platforms profit from subscriptions and ads. We profit from SaaS subscriptions. Everyone is advertising something. And that's okay. I hope you found this article useful.
    - pc86 6y ago
      Absolutely, I have no problem with content marketing and while I'm not your customer or in the market for your solutions, I did think it was an interesting article. I was just responding to the criticism(?) that this was "just" an advertisement.
s_severus 6y ago
This article, whilst quite clearly an advertisement ("this is so complex - use our service!"), actually does a decent job of illustrating just a tiny fraction of the complexity of correctly modelling an e-commerce system. For the past 2 years I've been working full-time on an open-source headless e-commerce framework (https://www.vendure.io/ https://www.vendure.io/) so I can attest to this. Small illustrative example: I have spent the past 2 weeks re-working our tax handling to handle scenarios like this: - An order contains 5 products, some of which have different tax rates. Order total is $100. - A $10 coupon code reduces the order to $90. - The order is completed and shipped. Later on the customer wants to return and get a refund on one of the items. How much do you refund? The answer is that you need to prorate (distribute) the $10 discount proportionally over the 5 items, so that when refunding, this order-wide discount is taken into account. After extensive research (much of which I wrote up here: https://github.com/vendure-ecommerce/vendure/issues/573 https://github.com/vendure-ecommerce/vendure/issues/573), I was rather surprised to find that the majority of well-known OSS e-commerce frameworks don't handle this properly. On top of the things mentioned there are a whole host of other major topics of concern, e.g. * stock control & tracking * fulfillment & shipping integration * payment integration * promotions * account management (email verification, password resets etc) * product images / asset management * multiple sales channel, multi-currency, multi-language support For those interested, my project is approaching v1.0 so I've just about got a handle on all of the above. Phew!
- arichard123 6y ago
  I agree. I've spent 14 years building ecommerce sites, and the hardest thing for me was dealing with bundles. My client sells cot beds and mattresses, that come from separate suppliers as bundles. And once the bundle feature was added they went to town with it, but it makes stock hard, it makes forecasting hard, it makes promotions hard, it makes shipping hard. It's much easier when you sell items in multiples of 1, with each item priced and discounted individually. Good luck with your project.
- thesimon 6y ago
  > Order total is $100. - A $10 coupon code reduces the order to $90. - The order is completed and shipped. Later on the customer wants to return and get a refund on one of the items. How much do you refund? Especially fun if the minimum order value for the coupon code was $99 and the product returned costs $5.

129 points by robric 6y ago

I really enjoy this type of article that shares a data model for a common type of project. I'd love to see more examples for different common project types.

digaozao 6y ago
Yes. I wish there was more of that. Sometimes I get some initial ideas of data models from here: http://www.databaseanswers.org/data_models/index.htm http://www.databaseanswers.org/data_models/index.htm
- robric 6y ago
  Here are some data models for specific e-commerce use cases from that site: http://www.databaseanswers.org/data_models/top_ten_data_models_e_commerce.htm http://www.databaseanswers.org/data_models/top_ten_data_mode.... Thanks for sharing this.

rafaelturk 6y ago

If I ever venture into creating my own eCommerce platform I'd probably use NoSQL

tekstar 6y ago
You're going to want to run analytics, and to do that you need comparable data. Legacy data was a huge problem for the e-commerce giant I used to work at, even in The SQL database with a schema. NoSQL would be a silly non starter for any serious data modelling effort after a couple years of migrations outages, and code changes.
dragonsh 6y ago
eCommerce needs a hybrid data model. There are inherent relationships between various parts of eCommerce entities and it becomes very hard if just using noSQL with all the integrity constraints defined in application. This is one of the reason many companies moved back to relational model after dabbling with noSQL. I believe the best way to model it is to use something like PostgreSQL with jsonb docs for some type of data. If you are interested in noSQL database may be give a try to reactioncommerce (acquired recently), you will begin to hit limitations soon, given integrity constraint and ACID compliance becomes very important for some type of eCommerce data e.g. changing prices and inventory in real time which are consistent and correct. It becomes extremely hard and complex to manage it in application code, which comes free with relational database model.
forgingahead 6y ago
If I may share some advice - don't bother. As another commenter pointed out, relationships are inherent in a lot of the data you'll store for your platform, and for those elements that you'd like to keep the full data hash, just use jsonb in Postgres. Once you're at several million in revenue, then you can think about splitting off some parts of your data into document storage solutions for certain efficiency reasons, but that is by no means necessary. This is a great video on the topic: https://www.youtube.com/watch?v=HaEPXoXVf2k https://www.youtube.com/watch?v=HaEPXoXVf2k
deleted 6y ago
[deleted]

stevesimmons 6y ago

This is great, and worth comparing with, for instance, the Magento data models [1] (from Anna Volkl). Echoing the comment from rafaelturk about NoSQL, I'd love to see more info on how various industry verticals use DynamoDB as their primary store. [1] https://anna.voelkl.at/wp-content/uploads/2016/12/ce2.1.3.png https://anna.voelkl.at/wp-content/uploads/2016/12/ce2.1.3.pn...

robric 6y ago
That data model is legendary.

huy 6y ago

For those who're curious, the tool to build the database diagrams is dbdiagram.io

tmikaeld 6y ago

While Fabric is certainly cool and I'd love to dig into it, not having any inclination of the pricing makes it impossible to know which client-base they are after. I'm guessing those that have grown out of Magento/WooCommerce or even Shopify and Ecwid. Which means enterprise pricing, ie. custom quotes.

robric 6y ago
You're right on the money. We are for businesses that have outgrown those platforms. We don't surface pricing now. We need to keep our pricing flexible since we don't force businesses to purchase our entire platform like monoliths such as Salesforce and Oracle Commerce Cloud do. Instead of purchasing Fabric's entire headless commerce platform/product package, many businesses purchase individual or a couple products to start. They integrate these into their existing systems, then purchase more products (SaaS) down the road and integrate those. This increases time to value and removes the need for a total replatform. For instance, a multi-billion dollar company just purchased our PIM (https://fabric.inc/pim https://fabric.inc/pim) to start. They also want us to help with system integration. The needs and use cases are just too varied to offer blanket pricing at this point.

pbourke 6y ago

The OMS is a pretty crucial part of an e-commerce system. I’d outsource a great many other things before that.

revskill 6y ago

Where's the inventory management ? If i make a sale order, where to substract from inventory count ?

rglullis 6y ago
> Where's the inventory management? Salesforce /me ducks
robric 6y ago
This article did not explore that. However, order management systems by headless commerce platforms (not Salesforce) do a good job of handling this in an intuitive way.

lovetocode 6y ago

This is clearly an advertisement.

pc86 6y ago
Easily 50% of the articles on HN are advertisements. If you remove news articles and personal blogs that jumps to about 98%.
- robric 6y ago
  We like to create content about alternative solutions and then plug our e-commerce SaaS. This is referred to as content marketing. As for the blogs and news articles you speak of, these platforms profit from subscriptions and ads. We profit from SaaS subscriptions. Everyone is advertising something. And that's okay. I hope you found this article useful.
  - pc86 6y ago
    Absolutely, I have no problem with content marketing and while I'm not your customer or in the market for your solutions, I did think it was an interesting article. I was just responding to the criticism(?) that this was "just" an advertisement.

s_severus 6y ago

This article, whilst quite clearly an advertisement ("this is so complex - use our service!"), actually does a decent job of illustrating just a tiny fraction of the complexity of correctly modelling an e-commerce system. For the past 2 years I've been working full-time on an open-source headless e-commerce framework (https://www.vendure.io/ https://www.vendure.io/) so I can attest to this. Small illustrative example: I have spent the past 2 weeks re-working our tax handling to handle scenarios like this: - An order contains 5 products, some of which have different tax rates. Order total is $100. - A $10 coupon code reduces the order to $90. - The order is completed and shipped. Later on the customer wants to return and get a refund on one of the items. How much do you refund? The answer is that you need to prorate (distribute) the $10 discount proportionally over the 5 items, so that when refunding, this order-wide discount is taken into account. After extensive research (much of which I wrote up here: https://github.com/vendure-ecommerce/vendure/issues/573 https://github.com/vendure-ecommerce/vendure/issues/573), I was rather surprised to find that the majority of well-known OSS e-commerce frameworks don't handle this properly. On top of the things mentioned there are a whole host of other major topics of concern, e.g. * stock control & tracking * fulfillment & shipping integration * payment integration * promotions * account management (email verification, password resets etc) * product images / asset management * multiple sales channel, multi-currency, multi-language support For those interested, my project is approaching v1.0 so I've just about got a handle on all of the above. Phew!

arichard123 6y ago
I agree. I've spent 14 years building ecommerce sites, and the hardest thing for me was dealing with bundles. My client sells cot beds and mattresses, that come from separate suppliers as bundles. And once the bundle feature was added they went to town with it, but it makes stock hard, it makes forecasting hard, it makes promotions hard, it makes shipping hard. It's much easier when you sell items in multiples of 1, with each item priced and discounted individually. Good luck with your project.
thesimon 6y ago
> Order total is $100. - A $10 coupon code reduces the order to $90. - The order is completed and shipped. Later on the customer wants to return and get a refund on one of the items. How much do you refund? Especially fun if the minimum order value for the coupon code was $99 and the product returned costs $5.