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

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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461