a database schema issue
Posted: Sun Nov 02, 2003 7:35 am
I have built a web database driven site that works with php and mysql. This is a on-line store catalog and the schema of the database is as follows:
There are 5 levels of product organization going downwards: Product Categories, Product Groups (each group belongs to a Category), Product Brands (each Brand belongs to a Group), Product Types (each Type belong to a Brand) and finally Products (i.e., the product itself. Each Product belongs to a Product Type). Thus, the database consists of 5 tables (regarding the product hierarchy, because there are other tables for Customers, etc) which have a one to many relationship each one with its next one in the hierarchy.
Tables:
Product Categories
Product Groups
Product Brands
Product Types
Products
Below follow the field names that each table has:
table "Product Categories"
cat_id
cat_name
table "Product Groups"
product_group_id
cat_id (the category that the product group belongs to)
product_group_name
table "Product Brands"
brand_id
product_group_id (the product group that the brand belongs to)
brand_name
table "Product Types"
product_type_id
brand_id (the brand that the product type belongs to)
product_type_name
product_type_description
table "Products"
product_id
product_type_id (the type that the product belongs to)
product_name
product_description
product_photo
As one can see, the primary key for each table is an integer id which is used for inner joining the table with the next down in the hierarchy, e.g. a product group has a certain product_group_id and belongs to a certain category with id = cat_id, a product brand has a certain brand_id and belongs to a product group with id=product_group_id and it goes accordingly till the 5 th level (the Products table).
Therefore, the administrator of the catalog, in order to add a new product, he has to fill in the product type that this product belongs to (and this product type belongs to a product brand, which belongs to a product group, which belongs to a product category).
Nevertheless, in the catalog there are some products that do not belong to a product type, or a product brand, or a product group. They just belong to a product category. In other words, these products are defined by the 1st and the last table, or by some tables fewer than the total tables. And my question is: What is best approach for handling these products in the database and what kind of relationships (and additional fields) should I add to the tables?
I considered one approach which is described below with an example:
Say, a product named "Deluxe Aquarium" is described only with its category which is named "Aquaria" that it belongs to, and its product details, i.e. with the 2 tables "Product Categories" and "Products". The administrator of the catalog fills in the intermediate tables, using the same name - that is "Aquaria" - for the Product Group, the Product Brand and the Product Type that this produc will belong to. i.e., he creates a product type named "Aquaria", in which the product Deluxe Aquarium" will belong to, then he creates a product brand named "Aquaria" in which the product type "Aquaria" will belong to and finally he creates a product group named "Aquaria" in which the brand "Aquaria" will belong to. This product group will belong to the category "Aquaria". Is there another better approach ?
I appreciate any help
There are 5 levels of product organization going downwards: Product Categories, Product Groups (each group belongs to a Category), Product Brands (each Brand belongs to a Group), Product Types (each Type belong to a Brand) and finally Products (i.e., the product itself. Each Product belongs to a Product Type). Thus, the database consists of 5 tables (regarding the product hierarchy, because there are other tables for Customers, etc) which have a one to many relationship each one with its next one in the hierarchy.
Tables:
Product Categories
Product Groups
Product Brands
Product Types
Products
Below follow the field names that each table has:
table "Product Categories"
cat_id
cat_name
table "Product Groups"
product_group_id
cat_id (the category that the product group belongs to)
product_group_name
table "Product Brands"
brand_id
product_group_id (the product group that the brand belongs to)
brand_name
table "Product Types"
product_type_id
brand_id (the brand that the product type belongs to)
product_type_name
product_type_description
table "Products"
product_id
product_type_id (the type that the product belongs to)
product_name
product_description
product_photo
As one can see, the primary key for each table is an integer id which is used for inner joining the table with the next down in the hierarchy, e.g. a product group has a certain product_group_id and belongs to a certain category with id = cat_id, a product brand has a certain brand_id and belongs to a product group with id=product_group_id and it goes accordingly till the 5 th level (the Products table).
Therefore, the administrator of the catalog, in order to add a new product, he has to fill in the product type that this product belongs to (and this product type belongs to a product brand, which belongs to a product group, which belongs to a product category).
Nevertheless, in the catalog there are some products that do not belong to a product type, or a product brand, or a product group. They just belong to a product category. In other words, these products are defined by the 1st and the last table, or by some tables fewer than the total tables. And my question is: What is best approach for handling these products in the database and what kind of relationships (and additional fields) should I add to the tables?
I considered one approach which is described below with an example:
Say, a product named "Deluxe Aquarium" is described only with its category which is named "Aquaria" that it belongs to, and its product details, i.e. with the 2 tables "Product Categories" and "Products". The administrator of the catalog fills in the intermediate tables, using the same name - that is "Aquaria" - for the Product Group, the Product Brand and the Product Type that this produc will belong to. i.e., he creates a product type named "Aquaria", in which the product Deluxe Aquarium" will belong to, then he creates a product brand named "Aquaria" in which the product type "Aquaria" will belong to and finally he creates a product group named "Aquaria" in which the brand "Aquaria" will belong to. This product group will belong to the category "Aquaria". Is there another better approach ?
I appreciate any help