Re: normalization problems

In article <URI06.231$2X4.18223_at_petpeeve.ziplink.net>,   "David Cressey" <david_at_dcressey.com> wrote:
> 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
>
>

David,

Agreed - my solution is merely a way of implemeting meta-data in the database design.

The problem with representing each category's in a separate table is the proliferation of tables and that the SQL statements will need to vary for each category.

Some degree of control would be required to ensure that categories are not added willy-nilly by programmers. This is the path to disaster!

This can be kept under the control of the DBA by restricting access to the relevant tables to the DBA only. Thus new categories can only be added by the DBA after they have been thoroughly evaluated and documented.

Regards

Vaughan

--
Vaughan Powell MCDBA, MCSD, MCSE
Data Architecture and Applications Design Manager
BuildOnline


Sent via Deja.com
http://www.deja.com/