"H L" <[EMAIL PROTECTED]> wrote on 04/03/2006 01:53:37 PM: > > >The solution is to redesign your tables. You need to split into separate > >columns the values you want to maintain. You do not want to keep the "flat > >file" design you are currently trying to use. > > > >CREATE TABLE calendar ( > > objectid, > > year, > > dayofyear, > > ... other fields... > >) > > > >CREATE TABLE price ( > > objectid, > > year, > > dayofyear, > > price > >) > > > >Having a separate column for each day of the year may make sense to a > >person but as you have discovered, it is extremely difficult to use for > >any kind of ad-hoc querying. A more normalized data structure will be > >almost as efficient in space usage but 1000s of times more efficient for > >querying. There is no simple way to write a query that spans years with > >the table structures you currently have. > > > >Shawn Green > >Database Administrator > >Unimin Corporation - Spruce Pine > > > Hi thanks for the quick response! > > Well i think you are right about this. I will probably have to redesign the > database. What do you think of using DATE instead of year,dayofyear or is > there problems i could get into then. It would be great to be able to use > select from price where date>=somedate AND date <= tosomedate > > /Henrik >
You could use a single date column for (year, Julian date) but if you are constantly querying on the Julian date (based on your application or other needs) then having that column would be a good thing. Without a column for Julian date, you could not index that value either by itself or as part of another index. Which way works better for you really depends on *your* application's needs and I cannot guess all of them. However, you really should normalize that data first. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
