Re: normalization problems

How does the solution given compare with something to the effect of the following:
(please forgive lousy documentation, I have no training in this)

Product
PK product_id
FK category_id

Category
PK category_id

Category_Numeric_Attribute
PK cat_num_att_id
FK category_id
name

Category_String_Attribute
PK cat_str_att_id
FK category_id
name

Category_Date_Attribute
PK cat_dat_att_id
FK category_id
name

Date_Attribute
FK cat_dat_att_id
FK product_id
value

String_Attribute
FK cat_str_att_id
FK product_id
value

Numeric_attribute
FK cat_num_att_id
FK product_id
value

And so on, for as many different types of attribute data you are going to need. Then, rather than performing 1 query to get all the parameters of a product, you perform X queries, where X is the number of types of parameter data you have configured.

I seem to remember coming across this problem when I was doing an inventory system, and coming to this conclusion. I'd be interested to hear it's good and bad points.

Jason Morris
Systems Analyst
Alberta Cancer Board

"David Cressey" <david_at_dcressey.com> wrote in message news:URI06.231$2X4.18223_at_petpeeve.ziplink.net...
> Vaughan,
>
> I've seen the kind of approach you outlined to Karen, in a large
 application
> in the
> field. My reaction is more on the practical level than on theoretical
> level.
>
> An approach like the one shown in the Category Attribute table
> has its plusses and minuses. The plusses are that it does indeed solve
 the
> problem that Karen outlined, if I read her correctly. And it's OK on
> performance.
>
> It also allows new relationships to be encoded by adding new rows to the
> Category Attribute table without altering the schema. From the point of
> view of the programmers, this is a plus, because now they can
> implement relationships that they discover on the fly, without waiting
> for a DBA to approve a schema change.
>
> From my point of view, however, allowing undocumented relationships
> to be stored in a database undermines the purpose of having a schema
> to begin with.
>
> If you do that, and if you expect the result to be as useable by a
> database expert who comes on board, then you have to go through the
> task of documenting all the relationships that are implemented in the
> Category Attributes table. If you don't then the data in the database
> will become mysterious and unworkable just as fast as it once did
> in the days of files, before databases came along.
>
> In the case I saw, the only people who understood the data were
> the programmers. It cost a lot of effort, and a lot of money, to
> rediscover the relationships in the data.
>
> It's a trade off.
>
>
> Vaughan Powell wrote in message <91vcfl$vtc$1_at_nnrp1.deja.com>...
>
> >This is just a 'get you started' suggestion, not a complete database
> >design - you will have to follow through my ideas and see if they a
> >suitable for your application.
> >
> >You could generisise the Product Category details with the
> >Category_Attribute table:
> >
> >Category_Code -- Primary Key
> >Attribute_Code -- Primary Key - -- e.g. response_cycle_analysis
> >Attribute_Type -- e.g. datetime, numeric, string
> >Attribute_Label -- e.g. Response Cycle Analysis
>
>
>
Received on Sat Jan 06 2001 - 01:20:10 CET