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.

  • 1
    what if a vendor sells a product to another vendor? Jan 22 '13 at 3:14

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:

-- id
-- name


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:


    --attribute_id (foreign key of attributes.id)

(edited for spelling)

  • When you say that the product attributes should be placed in a separate table, I'm a little confused as to how the product_attributes data would be structured. What exactly would the product_attributes.name and product_attributes.value fields be storing? Since attributes.product_id would be a foreign key of products.id, would there be the same number of records in product_attributes as products? I understand your second example's attributes table of course, but again I am unsure as to the purpose of the product_attributes table.
    – Qcom
    Jan 22 '13 at 20:44
  • For example, if you had a product called "Hard Hat" that had two attributes: color and closure_type. The product_attributes for that product would be (name - value): color - Yellow and closure_type - Strap. Each product could have multiple records in product_attributes (one for each attribute).
    – yohaas
    Jan 22 '13 at 23:57
  • This is a variant on the EAV model; you should avoid this unless you're going to have a large number of products with different attributes that you genuinely can't design in advance. Jan 23 '13 at 12:05
  • @ChrisSaxon That's a good point. I assumed based on the question that the attribute types would be pretty varied, but if there are only slight differences, this may over complicate things.
    – yohaas
    Jan 23 '13 at 14:45
  • But if we have 1000 products we have atleast 10,000 attributes. will that decrease the speed of site? if does, how can we do in that situation?? Jun 11 '14 at 9:50

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:

other common

other attributes

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.

  • This could be modelled nicely in PostgreSQL using table inheritance. Jan 23 '13 at 12:10

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.