How many ways can we database a store?

Sep 22, 2026

It's the sort of simple question that can easily come up in some intro to database class as a homework project. It can even be an interview question for entry level data analyst roles that require SQL knowledge. For most of the people reading this I'm sure you can imagine at least one, probably two or three way that you'd start to put such a database together.

Obviously you'd need a table of products to sell, a transaction table to keep track of orders and the various states. After that you'll likely need some important but not critical things. For example, you'd likely want something to track individual users with their personal information (email, address, etc.).

But beyond those core tables, things start getting blazingly complex. You may want to store payment information in a secure way, where "secure" may mean PCI compliance, or something else. You may want to track shipping status with various carriers. You somehow need to model various kinds of returns (full or partial). You may want to handle taxes. You may need to deal with inventory and interact with a warehouse.

To take things even further, maybe your backend architecture has weird constraints on it. Maybe you're building something that's got tons of microservices bouncing around. Maybe you're linking into a big enterprise inventory system. Maybe you're one of the most popular e-commerce platforms on the planet but have crammed the entire database backend into a blog's database schema.

EAV models

Entity-Attribute-Value models are a style of modeling data where you have entities (like laptops for sale) that can have a collection of attributes (like CPU and screen size) that can take values ( Intel xyz CPU, 2k 15" screen).

The basic idea being that laptops have a list of attributes consumers care about that are very different from vegetables, but either one can be modeled by the same underlying approach of creating entities and linking attributes and values to them. Everything starts out as an Entity, Attribute, or Value type and extends from there so you can keep adding new twists and turns to the data model without really needing to do a DB migration.

In theory you could go all-in and have just three tables of E, A, and V but it'd be really annoying to use and a pain to index and maintain performance. In practice entities (laptops, vegetables, cars, etc.) all live in the catalog_product_entity table. Attributes are associated with those entities via the use of a table that link attribute sets to a given entity.

What makes Magento's schema confusing to understand is that since values have types, string, integers, floats, etc., the system spins off a separate value tables by type – meaning you get a catalog_product_entity_varchar table in addition to the catalog_product_entity_int table that get referenced against the catalog_product_entity table when tracing through all the attributes of an entity. A similar sort of pattern exists for tables like customer_entity which would store customer account data.

This is probably the most formal and abstract way of doing things. You'll find examples of it in e-commerce platforms like Magento (as someone explains here). As you would expect, working with the data for analytics requires a lot of JOINs, especially LEFT JOINs, because you have to chain the tables together and some entities will have some attributes and not the other.

Pssht, we'll just use a CMS model

The canonical antithesis to the very formalized EAV world is the Woocommerce ecosystem, which is a plugin used on top of the Wordpress blog platform and literally reuses Wordpress's own database schema of posts, metadata and terms to create what is the most popular open source e-commerce platform on the planet. Here, the bulk of the core data lives by hijacking the database from a blogging platform and just outright abusing it to do other things.

To get a mere taste of the madness within, all products are put into the wp_posts table with post_type="product". Orders are ALSO stored under wp_posts but with post_type="shop_order". Prices, SKUs and other associated metadata on the product are put into the wp_postmeta table and you join them back using the post_id. User data for registered users are stored under the native Wordpress user table.

Side note, as of 2023-ish, the overall architecture got too burdensome even for the devs and they migrated to "High-Performance Order Storage" (HPOS) by default where orders got pulled out of wp_posts and into dedicated wp_wc_orders, wp_wc_order_addresses, and related tables. Products are still living in wp_posts.

I've worked with many different databases over the years, enough that I can take a decent stab at understanding how many systems work just by seeing table dumps. Woocommerce's hijacking of WP's tables is ridiculous enough that I struggled to make sense of it without looking up explanations. The system works well enough for smaller e-commerce outfits who want to self-host their own platform, but are happy using the web interface without ever really peaking at what the backend is doing.

Something in the middle

One problem with very formal systems like Magento is that it is often a giant pain in the butt to get things going. While you can probably take defaults for most things, you eventually still have to do the legwork and make decisions about how to model your items and attributes. If you just want to sell a handful of products that are all very similar, it's sometimes nicer to run a platform that takes some of that flexibility away in the name of simplicity.

You'll find various platforms like Spree and Solidus which gets rid of most of the Entity abstraction in the EAV model by materializing most Entities and Attributes into the table structure. So you have your spree_products table that stores the main products, a couple of spree_products_properties/taxons/etc tables that allow many-to-one relationships for various attributes. You have the spree_orders table that has what you'd expect, and so on. It's really easy to reason about, but if you're adding new features you're going to have to do a DB migration to add/modify tables.

Here, analytics is very straightforward because you have expected objects living in tables and they inter-link with foreign key IDs. About the most complicated thing in your way is juggling any many-to-many relationships that may pop up in between tables.

And then let's half-use the database

At some point, frontend developers who always seem to have a grudge against writing SQL realized that databases allow you to shove arbitrary JSON fields into databases now (like in Medusa), or have ORM libraries that let devs do all their work in TypeScript and manage the database migrations and use GraphQL to do querying for you (like with Vendure). This in turn means you can generally use fewer tables by smashing attributes and values into the main entity table.

So while it used to be that you either did EAV-like semantics if you hated the idea of having to do database migrations, or you picked one of the other ways where big changes would require adding a table or field in. Now, there are platforms that allow you to be more "code first" by taking advantage of newer that lets you get away with more.

Depending on the specifics, life as an analyst on these more modern codebases has its own quirks. JSONB is a bit of a pain to handle in SQL due to the nature of it being able to change at any time – great for developers, less so for us analysts.

All implementations add their own complexity

The challenge of analyzing data is that even if you went to a hundred different companies that used any one of the above platforms to run their business, doing analytics on them would still be a largely bespoke challenge due to the little implementation details that lie underneath the architecture. For example, one place may model prices as floats because they store things in dollars while another uses integers because they write everything in cents or use a currency that doesn't have decimal places. Even people selling the same item class like laptops will disagree on what specs and attributes they want to input into their system.

Thanks to all the little implementation quirks, any one detailed analysis or report done in one system is almost completely irrelevant in any other context without a lot of translation work. We know this because it's our lived experience in working directly on these tables every day. It's why we ask for metrics definitions and previous queries to reference. I'm pretty sure that most people without that direct experience don't even realize it is a problem.

Even "simple" problems of selling stuff can come up with all these different ways of doing it. Just imagine the amount of unbounded chaos that exists for the data models of most other bits of software.

Subscribe!

Standing offer: If you created something and would like me to review or share it w/ the data community — just email me by replying to the newsletter emails.

Guest posts: If you’re interested in writing something, a data-related post to either show off work, share an experience, or want help coming up with a topic, please contact me. You don’t need any special credentials or credibility to do so.

"Data People Writing Stuff" webring: Welcomes anyone with a personal site/blog/newsletter/book/etc that is relevant to the data community.

Counting Stuff Official Forums: Discuss posts, or other data topics with the community.

I’m Randy Au, Quantitative UX researcher, former data analyst, and general-purpose data and tech nerd. Counting Stuff is a weekly newsletter about the less-than-sexy aspects of data science, UX research and tech. With some excursions into other fun topics.

All photos/drawings used are taken/created by Randy unless otherwise credited.

All Tuesday posts to Counting Stuff are always free. The newsletter is self hosted. Support from subscribers is what makes everything possible. If you love the content, consider doing any of the following ways to support the newsletter:

Consider a paid subscription – the self-hosted server/email infra is 100% funded via subscriptions, get access to the subscriber's area in the top nav of the site too
Send a one time tip (feel free to change the amount)
Join the Approaching Significance Discord — where data folk hang out and can talk a bit about data, and a bit about everything else. Randy moderates the discord. We keep a chill vibe.
Get merch! If shirts and stickers are more your style — There’s a survivorship bias shirt!