Re: normalization problems

From: Mark Greene <greenemj_at_my-deja.com>
Date: Thu, 21 Dec 2000 17:42:41 GMT
Message-ID: <91tfef$fea$1_at_nnrp1.deja.com>


In article <3a41f67c.6688045_at_news.ntlworld.com>,   kosmond_at_softhome.net 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.

So you are looking at something like this:

Product



prod_code
prod_name
(rest of table...)

Category



cat_code
cat_name
(rest of table...)

Prod-Cat



pc_code
prod_code
cat_code

Vendor



Vendor_code
pc_code
(rest of table...)

where the prod-cat table contains all of the valid product-category combinations, and then all of the attributes specific to just products or categories listed in those seperate tables, yes?

Yes, this introduces a level of indirection in that you cannot go from a Vendor directly to the product or category information. The alternative, however, is to combine all the product and category information into a single table, which would not be much (if any) better from a performance stand-point and a step backwards with the normalization.

HTH

--
Mark


Sent via Deja.com
http://www.deja.com/
Received on Thu Dec 21 2000 - 18:42:41 CET

Original text of this message