Date: Wed, 8 Mar 2006 12:53:10 -0600
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Ruben Rubio Rey <ruben@rentalia.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Is good idea an array of 365 elements in a cell of a table,
	in order to perform searchs?
Message-ID: <20060308185310.GG45250@pervasive.com>
References: <440EEA14.70606@rentalia.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <440EEA14.70606@rentalia.com>
User-Agent: Mutt/1.5.11

If you need to compare stuff on a day-by-day basis, I think you'll be
much better off just expanding stuff into a table of:

item_id     int NOT NULL
, day       date NOT NULL
, capacitiy ...
, price_per_day ...
, price_per_week ...
, PRIMARY KEY( item_id, day )

(Note that camel case and databases don't mix well...)

Sure, you're de-normalizing here, but the key is that you're putting the
data into a format where you can easily do things like:

SELECT sum(capacity) FROM ... WHERE day = '2006-12-18';

Trying to do that with arrays would be noticably more complex. And if
you wanted to do a whole month or something? Yeck...

BTW, another option is to roll price_per_15_days and price_per_month
into a different table, since you'd only need 24 rows per item. Might be
worth the trade-off in complexity depending on the specifics of the
application.

On Wed, Mar 08, 2006 at 03:28:36PM +0100, Ruben Rubio Rey wrote:
> Hi,
> 
> Im having a dude with a new inplementation in a web site.
> The ojective is create a search as fast as possible. I have thought two 
> possibilities to do that:
> 
> I have several items. Those items has 1 or more of capacity. Each 
> capacity, has several dates (From 1 january to 10 of april, for 
> example). The dates covers 366 days, the current year, and they are 
> indeterminated ranges. Per each date, it has price per day, per week, 
> per15days and per month.
> 
> I have designed two possibilities:
> 
> First:                                                               
> IdItem   StartDate    EndDate    Capacity   PricePerDay   PricePerWeek* 
>  PricePer15days*    PricePerMonth*
>    1         1-1-2005     10-1-2005         2                100       
>             90                     85                           80
>    1       11-1-2005     20-1-2005         2                105       
>             94                     83                           82
>    1       21-1-2005       5-2-2005         4                405       
>           394                   283                         182
>    2       ...
> Right now arround 30.000 rows, in one year is spected to have 60.000 rows
> 
> * In order to compare right, all prices will be translated to days. 
> Example, PricePerWeek will have the Week Price / 7 and go on
> 
> Second
> IdItem   Capacity   Days                                               
>                        Week       15Days       Month       Year
>   1              2          [Array of  365 values,  one per day of 
> year]     [ .Array. ]   [ .Array. ]   [ .Array. ]   [ .Array. ]
>                               ^__ Each item of array its a price
> 
> Right now arround 2.500 rows. in one year is spected to have 5.000 rows
> 
> I have to compare prices or prices and dates or prices and dates and 
> capacity or capacity and prices
> 
> I have no experience working with arrays on a table. Is it fast?
> Witch one do u think will have better performance?
> Any good idea?
> 
> I hope this is enouth information.
> Thanks in advance,
> Ruben Rubio Rey
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
> 

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461