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!