Thread: Is good idea an array of 365 elements in a cell of a table, in order to perform searchs?

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

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