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