Product Catalog: One big table or many related tables?

Brandon Hassler

My Current Situation

I've got an ec-commerce with roughly 13,000 products that largely runs on two tables within my Xano database:

Table 1: Products (Name, price, SKU, URL, etc)

Table 2: Product Categories (Each product in Table 1 has a relationship with this table as it points to the appropriate category)

What I'm now wanting to do is add product metadata/attributes to each product to make easier for users to filter through the products they don't care about. So for example, products that are monitors will have attributes like so:

Size: 27"

Resolution: 1920 x 1440

Refresh Rate: 144 Hz

Panel Type: IPS

A Potential Problem

Normally I would just add these additional fields as columns inside Table 1, but the complication is that each product category has different attributes. For example, a storage drive might have the following attributes:

Capacity: 1 TB

Type: SSD

Cache Memory: 256 MB

Possible Solutions

I have roughly 30 categories of products in my catalog, each with their own unique set of attributes. In my mind, I have 3 solutions:

Solution A

I could add all of those attributes as fields in my main Table 1, but my worry is that may make the table a bit "heavy" in terms of searching though it every time a user makes a call.

Solution B

I could create a table for each category and within those tables would be the correct attributes and then use a join or add-on to tie the tie together when the user is searching for products. My worry here is that could mean stringing 30+ tables together in a relationship and I just haven't fully thought out the logistics here.

Solution C

I would create a "Table C" that would house ALL of the product attributes for all categories. So the fields might look something like

monitor_size:

monitor_dimensions:

monitor_panel:

storage_capacity:

storage_type:

etc.

Similar to if this was all in the main Table 1, for each product all of these fields would be empty except for the ones it applied to.

My gut tells me Solution B is likely the best path, although parts of me say it's not much better than just dumping them all into the big Table 1 with everything else. This type of data never changes. The only thing that changes is the price of the product (which is in Table 1) so my assumption is that by housing the more permanent product data in a separate table (Table C) I could have that table cached for a long period of time which could potentially speed up the requests made to my database.

Am I overthinking this process? Which direction would be best practice? Thank you!

Ray Deck

The stress here I think comes from wanting "one ring to rule them all." I propose that you might split your problem.

You may want to have a primary "document" structure where each product has attributes, and you keep track of those attributes in a JSON field with key-value pairs for a flexible schema. That becomes your primary "source of truth".

For the purpose of searching and filtering, you would make additional tables (or use external services, like Algolia) that structure data in a form that is more conducive to search. Your data sync processes (likely over a background task) keep these two in synch by distributing data from your "source of truth" to your search and analytics-oriented structures. These structures would be easier you could tear down, restructure and replace depending on your search needs because you wouldn't worry about data integrity or loss - your source of truth stands separate.

We sometimes call these use cases the "database" vs the "data warehouse."

Brandon Hassler

Great thoughts as always, Ray!

If I understand correctly, you're suggesting my "Table 1" become a master record with every detail about every product. So in the above scenario, all of those attribute fields would be a part of Table 1. However, Table 1 would no longer become the table that is used by my users to find products. I would instead create something like a table for each category which would be similar to Table 1 but only include the products within that category. The logic being that instead of users constantly searching one huge table with 13K products, they are instead searching a smaller table with say 700-1300 products. Is that correct?

Does this conflict with the thought of not having duplicate fields/values in our database (hence the whole relational tables stuff) or does it not apply in this case since Table 1 simply becomes a master table for internal use?

For more context to anybody reading, here is what my current Xano database looks like:

You'll notice a lot of the fields are related to pricing, which happen to be the fields that are updated quite often. The reason I highlight them to is to show what a large portion of the table they currently take up AND right now this pricing data is only for the United States. What I'm hoping to do this year is give users the option to view pricing by region. I'm guessing it may make more sense to move the pricing data to it's own table(s) long-term.

In terms of searchability, the price comparison site is largely broken into two sections, and I color-coded which fields are typically called for the API request being made:

The more I think about all this, the more I realize it's a complex issue due to the specific nature of my site and (price comparison sites seems to be more complicated behind-the-scenes vs a traditional e-commerce site) will likely benefit from some office hours time over on StateChange. Had to pause for a couple months because I've had so little time to devote to the site but things are starting to clear up so that I can get back to the Xano grind!

Still figured I'd drop some detailed context for any other readers out there who have/will encounter similar stuff!