Re: Database Design Pattern Question (help!)

From: David Rolfe <dwrolfe_at_orindaFRUITBATsoft.com>
Date: Tue, 07 Oct 2003 14:40:27 +0100
Message-ID: <3F82C24B.4000306@orindaFRUITBATsoft.com>

How viable this is depends on the volumes of data you are dealing with.

The problem is that TABLE_DETAIL will become very big very quickly indeed. You'll also find that the PK index will be enormous. Performance will start out ok until the extra 'columns' start being stored in different disk blocks. Then you have a situation where to retrieve one 'record' involves hitting different parts of the disk drive, which harms performance. Even if you have lots of memory you'll still have the overhead of the initial retrieval and multiple blocks being used to store what is actually one record.

Things you can do:

Make sure the table has a high value for PCTFREE and keep adding columns. You can't have written that much code yet as otherwise you wouldn't be considering normalising it. The repeated requests to add columns may be part of your de-facto development methodology and will sooner or later slow down or stop.

Create TABLE and TABLE_DETAIL but watch out for the size of the related indices and how much sort space is required to re-create them. Also watch out for performance. You might want to *test* clustering the table - this would at least 'encourage' all the data to be in the same set of blocks. Also try writing reports. See just how many joins you need to get data out of such a data structure! There's a good reason why this isn't done that often....

If you're feeling brave and ambitious create one table with about 100 columns of different data types (textcol_01, numcol_01, datecol_02 etc) and then define a view which names the columns your application uses. You could even create a small meta data table that explains which view column equates to which table column. Adding NULL columns to the end of

   a table in Oracle does not take up space provided all the columns in front of it are used. This is overkill if you only have one table.

Rich

---

Remove FRUITBAT for a valid Email address..

www.orindasoft.com - makers of OrindaBuild which generates Java JDBC access code for calling PL/SQL Procedures.

Scott Good wrote:
> So this is my problem. I originally designed the following table:
> (The real table is significantly longer. I shortened the table for
> this discussion)
>
> TABLE
> ---------
> TableId
> Ticker
> Quantity
> Currency
> Price
> MarketValue
> Exchange
> MarketCapitalization
> Beta
>
>
>
>
> example data:
>
> TABLE
> -----------
> TableId 1
> Ticker IBM
> Quantity 100
> Currency USD
> Price 84
> MarketValue 8400
> Exchange NYS
> MarketCapitalization 157809800000
> Beta 1.19
>
>
>
> It works fine except my boss keeps asking me to add more and more
> columns to the table such as PresentValue, ValueAtRisk... When he
> wants another column I go in and add another column to TABLE. These
> requests will go on indefinitely, (ie. I will be adding more and more
> columns until I leave the company) So my thought is to change the
> structure to the following:
>
> TABLE
> ---------
> TableId
> Ticker
> quantity
> Currency
> Price
> MarketValue
>
>
> TABLE_DETAIL
> ---------
> TableDetailId
> *TableId <---------------- Foreign Key
> Key <---------------- Indexed
> Value <---------------- Will be a Varchar(100)
>
>
> example data:
>
> TABLE
> -----------------------------
> TableId 1
> Ticker IBM
> Quantity 100
> Currency USD
> Price 84
> MarketValue 8400
>
>
>
> TABLE_DETAIL
> ---------------------------------------------------------------------
> TableDetailId 1 2 3
> *TableId 1 1 1
> Key Exchange MarketCapitalization Beta
> Value NYS 157809800000 1.19
>
>
> This way I can add as many properties as I want to TABLE_DETAIL.
>
> Is this a standard design pattern? Is my performance going to kill me
> when I try to do queries. (I will probably have approximatly 2
> million rows in TABLE and about 100 million rows in TABLE_DETAIL.
> What am I missing?
>
> If someone has any urls or docs that is somewhat related to this
> problem please let me know.

-- 
------------------------------------------------------------------------
Remove FRUITBAT for a valid Email address..

www.orindasoft.com - Makers of OrindaBuild
OrindaBuild generates Java JDBC access code for calling PL/SQL Procedures.

Received on Tue Oct 07 2003 - 08:40:27 CDT