Building a scalable e-commerce data model | Hacker News Reader

Building a scalable e-commerce data model

resources.fabric.inc

129 pointsrobric25 comments

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/) 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), 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!

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.

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

This is awesome OSS, thanks for sharing it and giving a specific example of the problems it solves. I also got a kick out of how you called the article an advertisement then went on to advertise your own project. Really though, nice OSS. I watched the video on your site and it looks like a cool solution for people getting started with e-commerce. Much more flexible and customizable than Shopify and BigCommerce.

OK ok, the irony of calling out the content marketing and then promoting my project is not lost on me :D

But to clarify, calling it an advertisement is not necessarily a condemnation. Content marketing articles can be fine when they actually contain some substance, which I believe this one does.

And thanks for checking out the project and your kind words.

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.

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

Here are some data models for specific e-commerce use cases from that site: http://www.databaseanswers.org/data_models/top_ten_data_mode.... Thanks for sharing this.

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

That data model is legendary.

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

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

> Where's the inventory management?

Salesforce

/me ducks

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.

This is clearly an advertisement.

Easily 50% of the articles on HN are advertisements. If you remove news articles and personal blogs that jumps to about 98%.

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.

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.

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.

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

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

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

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.

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.

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

Reply on news.ycombinator.com