Designing a Products database: One Products table or many separated by vendor?

Question

In order to prevent the unwieldiness of managing product data spread across a number of excel spreadsheets, I have set up a Products database.

My data is in spreadsheets because it was (somewhat) easy to export a tab-delimited text upload file for both the Amazon and Google e-commerce platforms.

But I've realized that I can achieve the same simple exporting goal through a relational database, minus the inconsistency headaches and plus the added benefit of an expressive query language.

Now my main question is how I should design my database; and, namely, how many tables my Products database should contain. I see three options here:

1. 1 table for all products. This seems to me like it would avoid redundancy, and thus feels like the best choice. Take an example: if I decide to add an attribute to the database at a later date, I would not have to do so for each vendor/category (see option 2 and 3). I am inclined to say that there is a downside because products of one category will have many NULLs in attributes meant for other categories, but I'm not sure if this is actually a negative.
2. 1 table for each vendor and its products. This was my instinctive first choice, but I don't find it to be the most logical division: what's critical in determining the uniqueness of a product's attributes is its category, not which vendor it was purchased from. Moreover, even though one vendor will often specialize in a certain kind of product, there will be category overlap if a vendor sells more than one kind of product.
3. 1 table for each kind of product (e.g. padlocks, chain, safety equipment). I think this is the most reasonable choice other than number 1, because it is almost a guarantee that one kind of product will require different attributes than another. The obvious con I see to this strategy is the difficulty of making the divisions. I'll take hard hats as an example. Should hard hats have their own table? Certainly not. (Unless of course your business is in hard hats.) So, moving up the category chain, should construction accessories have its own table? Maybe closer. How about safety apparel? That makes sense to me, but then there will be the same problem of the many NULLS as option 1 because hard hats will be lumped in with safety vests and goggles etc. etc.

Hopefully I'm not way off the mark with my suggestions. I didn't want to ask before giving my questions some thought, but I'm new to databases, so I'm sure I didn't make the most informed reasoning. I'm heavily leaning towards option number 1, but I'd love to hear advice on any, or if I missed an obvious strategy entirely.

Answer 1

I would go with number 1. Having a separate table for each vendor or each product type will be a nightmare when trying to run reports that need data from multiple product tables. You don't want to have to create a new table each time you add a vendor or product type. I understand your inclination coming from a spreadsheet, but with the ability to query the records easily, separating the products into multiple tables is not the way to go.

I am inclined to say that there is a downside because products of one category will have many NULLs in attributes meant for other categories, but I'm not sure if this is actually a negative.

To prevent this, attributes should be in a separate table.

For example, a partial table definition may be as follows:

products
-- id
-- name

product_attributes 
--product_id
--name
--value

attributes.product_id is a foreign key of products.id.

To further normalize it you could define the attributes in a separate table and have an attribute_id field instead of attribute_name:

    attributes
    --id
    --name

    product_attributes 
    --attribute_id (foreign key of attributes.id)
    --value

(edited for spelling)

Answer 2

I would go for option 1 possibly introducing option 3 if you have a large number of products with very different attributes. This will give you a "master" product table, holding attributes common across all (or most) products (e.g. name, price, etc.) and separate detail tables with more specific entries for each product type.

From your question, it sounds like you have (at least) two main classes of product, "hardware" (padlocks, chains, etc.) and clothing (hats). So you would have tables something like this:

PRODUCTS
id
type
name
other common

HARDWARE_PRODUCTS
product_id
weight
other attributes

CLOTHING_PRODUCTS
product_id
size
colour
other attributes

You could then have further child tables below hardware or clothing, if there's more specific details required or just add columns to these tables (which may be null for some clothing/hardware types). It's fine to have nullable columns, though if a large percentage of the columns will be null due to the product type most of the time, you should think about splitting the table into separate child tables, similar to above.

If you need to say which products a vendor sells, you can link them via a PRODUCT_VENDORS table.

Answer 3

I think there can be another idea. but I am not sure on how much it is successful.

TABLE "Product" - id, name, type(category), price, <common attributes>.

TABLE "Attribute" - att_id, type(category), name, type(radio,text,checkbox), values(common values in json)

TABLE "product_attr" p_id, details( (att_id:value), (att_id:value), etc in json)

The product have 1:1 join with the product details table.

On display, getting attributes in this synatax
attribute_list = ('att_id1'=>'name1', 'att_id2'=>'name2',... )
will be easier for displaying all attributes.