Re: Which Oracle book was this?

dba_222_at_yahoo.com writes:

> Years ago, I skimmed an Oracle book at the bookstore.
> I'm sure it was written by Tom Kyte.
>
> In it, he critiqued a system that would store data in
> rows, instead of simple columns!
> One criticism being that it performed very badly.
>
>
> So, instead of a typical table structure:
>
> field1
> field2
> field3
> field4
>
>
> There was a lookup table with field codes, and what they meant.
>
> Fieldcode_id
> Fieldcode_name
>
>
> The "data" table would be structured:
>
> Fieldcode_id
> value
> etc.
>
> Doing simple queries was of course much more complex.
>
> I remember reading words to the effect:
> "How did it perform? Horribly, pathetically, awfully!"
>
>
> I'm unfortunately dealing with such a system now,
> and would like to show this critique to the brass.
>
>
> Which book can I find this in please?
>
> Thanks a lot!
>
>

I'm not sure which of Tom's books it was, but you are likely to find something on his site asktom.oracle.com or you could even post a question there and he may respond (in time - he gets lots of questions).

I agree with the argument against attribute-value style tables. However, I do believe they can have a use and that it isn't an absolute 'never do it' situation.

I'm a very strong proponent of putting as much integrity constraint into the database as possible. All types should match as cloesly as possible the type of the data (e.g. do not use varchar2(4000) for all varcchar2 fields, use not null if it should never be null, use foreign keys wherever possible, use check constraints where appropriate, use date fields for dates etc. I constantly have arguements with other developers who argue this takes away from the flexibility of the design. usually, I argue that if you find your design isn't flexible enough, its because your analysis wasn't good enough in the first place.

Currently, I'm developing a replacement system for one that was poorly designed and where they have used attribute-value style tables. Its a mess and has been a problem for a long time. The new design has exttensive constraints and a lot of tables that only hold few rows of data.

However, I have got one attribute-value style table. The table is a configuration table that is used by the application to set certain behavior. It only has about 5 rows of data and is unlikely to have more than 10. It is not part of the main data model of the application and isn't used in the main select, update or insert operations. It does things like hold the e-mail address of who error reports are sent to, identifies if the database is a development, QA or production system (which controls things like where e-mail notifications are sent - to clients based on data in the client table or to the testing team or development team etc (to some extent, I expect newer Oracle features, such as conditional compilation and other features can now satisfy some of these requirements - just have to find the time to look into such stuff!)

For this type of use, I think an attribute-value table is OK. In general though, it should be avoided.

Tim

-- 
tcross (at) rapttech dot com dot au