Q

Designing a generic "Product" table

I work in a product-based company. I am planning to build a generic data mart for all our future customers. 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.

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.

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

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close