Ask the Expert

Designing a generic "Product" table

I am working in a product-based company. We are selling our products to various customers. Presently we are customizing our product solution based on the customer's business requirement. My role here is as a data modeler and I am planning to build a generic data mart for all our future customers.

I'll explain the scenario. We are selling our products to the manufacturing industries. Different customers will manufacture different products, e.g., Mercedez Benz produces cars and some other vehicles, Whirlpool produces washing machine, Nokia produces mobiles devices, etc. Each and every product type varies in their attributes from customer to customer.

Keeping this in mind we need to design our "Product" table generically, so that it can accomodate any type of products. Please suggest what is the best way to handle this situation.

    Requires Free Membership to View

When performing any data modeling, the best thing to do is to figure out what entities you wish to model and the attributes of those entities. From the above description, it should be obvious that you have two entities, CUSTOMERS and PRODUCTS. What is not so obvious is that you have a third entity at play, PRODUCT_ATTRIBUTES. So right away, I can see three tables that need to be built similar to the following:

CUSTOMERS
--------------------
cust_id
cust_name

PRODUCTS
-----------------
prod_id
prod_name

PRODUCT_ATTRIBUTES
------------------------------------
prod_id
attribute_id
attribute_name

Note that in the PRODUCT_ATTRIBUTES table, you will have an attribute for each product that each customer makes. When a customer makes a product, it will have attribute values. So you might employ a fourth table to encapsulate all of this information.

CUST_PRODUCT_VALUES
----------------------------------------
cust_id
prod_id
attribute_id
attribute_value 

In this way, each customer can have completely different products with different attributes. The CUST_PRODUCT_VALUES table has foreign key relations to the CUSTOMERS, PRODUCTS and PRODUCT_ATTRIBUTES tables.

This was first published in July 2006

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: