Re: Database Design Pattern Question (help!)

http://www.ixora.com.au/tips/design/meta-data.htm

Anurag

"Scott Good" <cnavin_Bloomberg_at_yahoo.com> wrote in message news:66908eb4.0310031231.6944a3bc_at_posting.google.com...
> 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.
Received on Fri Oct 03 2003 - 17:31:06 CDT