Re: normalization problems

Karen Osmond wrote:

> Hi,
>
> I am working on a database which contains data about Vendors and
> Products, and I'm having problems structuring the data.
>
> The Products fall into a range of categories (such as Budgeting &
> Planning, Knowledge Management, Business Scorecards...).
>
> Each of these categories have different attributes that will be
> associated with them. Each Product may fall into more than one of
> the categories.
>
> There will also be some statistical attributes that are the same for
> all products, regardless of category.
>
> The only way I can see of doing this is by having a Product table
> (containing the common statistics) with a one-to-many relationship to
> a Product-Category table with a one-to-one relationship to each of the
> category tables, but this seems awful!
>
> The Product-Category table would contain the Product Code and a
> Category identifier, so I could code around having to search through
> 16 category tables, if I did not know which category(s) a product was
> in.
>
> This seems like a really bad way of doing things - I'd be really
> grateful if someone could point me in the right direction!
>
> Thanks,
> -Karen Osmond.

Hi Karen,

I thing is a problem of subset:

CREATE TABLE Product
(prod_code ....,
 prod_name...,
 ....
PRIMARY KEY (prod_code) ... ) ;

CREATE TABLE Category1
(prod_code ....,
 ....
PRIMARY KEY (prod_code) ...
FOREIGN KEY (prod_code) REFERENCES Product ...,  ...);

CREATE TABLE Category2
(prod_code ....,
 ....
PRIMARY KEY (prod_code) ...
FOREIGN KEY (prod_code) REFERENCES Product ...,  ...);

The only problem is that you have to create a new table for every new category, and this can be a problem if the categories to manage are changing along the time.

Cheers,

Raimundo Lozano Received on Fri Dec 22 2000 - 12:09:25 CET