a database schema issue

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
vasilis
Forum Commoner
Posts: 40
Joined: Tue Apr 22, 2003 7:37 am

a database schema issue

Post by vasilis »

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 :)
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

A very minor variation on your own idea would be to create a "none" category in each of the intermediate tables. This might improve the data model a little: for example if a script looked at an intermediate table and found "none" rather than "aquaria" it's immediately obvious that there is no value.

You might need several none1, none2 etc branches if there are many products like this in different categories.
Post Reply