Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
what if a vendor sells a product to another vendor? –  Neil McGuigan Jan 22 '13 at 3:14

3 Answers 3

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.

share|improve this answer
    
This could be modelled nicely in PostgreSQL using table inheritance. –  a_horse_with_no_name Jan 23 '13 at 12:10

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)

share|improve this answer
    
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. –  Chris Saxon 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?? –  Eugine Joseph Jun 11 at 9:50

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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