In a relational data model, for normalization purposes, product category lookup, product sub-category lookup, product lookup, and and product feature lookups are are not merged as a single table. In a dimensional data modeling(star schema), these tables would be merged as a single table called PRODUCT DIMENSION for performance and slicing data requirements.
Example of Product Dimension: Product Category Lookup: Product Category Code Product Category Name DateTimeStamp 1 Apparel 1/1/2005 11:23:31 AM 2 Shoe 1/1/2005 11:23:31 AM
Product Sub-Category Lookup: Product Sub-Category Code Product Sub-Category Name DateTimeStamp 11 Shirt 1/1/2005 11:23:31 AM 12 Trouser 1/1/2005 11:23:31 AM 13 Casual 1/1/2005 11:23:31 AM 14 Formal 1/1/2005 11:23:31 AM
Product Lookup: Product Code Product Name DateTimeStamp Product Code Product Name DateTimeStamp 1001 Van Heusen 1/1/2005 11:23:31 AM 1002 Arrow 1/1/2005 11:23:31 AM 1003 Nike 1/1/2005 11:23:31 AM 1004 Adidas 1/1/2005 11:23:31 AM
Product Feature Lookup: Product Feature Code Product Feature Description DateTimeStamp 10001 Van-M 1/1/2005 11:23:31 AM 10002 Van-L 1/1/2005 11:23:31 AM 10003 Arr-XL 1/1/2005 11:23:31 AM 10004 Arr-XXL 1/1/2005 11:23:31 AM 10005 Nike-8 1/1/2005 11:23:31 AM 10006 Nike-9 1/1/2005 11:23:31 AM 10007 Adidas-10 1/1/2005 11:23:31 AM 10008 Adidas-11 1/1/2005 11:23:31 AM
Product Dimension: Product Dimension Id Product Category Name Product Sub-Category Name Product Name Product Feature Description DateTimeStamp 100001 Apparel Shirt Van Heusen Van-M 1/1/2005 11:23:31 AM 100002 Apparel Shirt Van Heusen Van-L 1/1/2005 11:23:31 AM 100003 Apparel Shirt Arrow Arr-XL 1/1/2005 11:23:31 AM 100004 Apparel Shirt Arrow Arr-XXL 1/1/2005 11:23:31 AM 100005 Shoe Casual Nike Nike-8 1/1/2005 11:23:31 AM 100006 Shoe Casual Nike Nike-9 1/1/2005 11:23:31 AM 100007 Shoe Casual Adidas Adidas-10 1/1/2005 11:23:31 AM 100008 Shoe Casual Adidas Adidas-11 1/1/2005 11:23:31 AM
