From: johansson.andersson@telia.com   
      
   I mean to put the price as a column in the sales tables doesn't solve the   
   problem because you would overwrite the previous price.   
      
   //Tony   
      
   "Erland Sommarskog"  skrev i meddelandet   
   news:XnsA442CB2B72BBEYazorman@127.0.0.1...   
   > Tony Johansson (johansson.andersson@telia.com) writes:   
   >> Assume I have a table called Sales in the dataware house where we have   
   >> the   
   >> four columns Month, Ice cream, City and quantity.   
   >> We have three dimensions where we have Month, Ice cream and City.   
   >> It could look like this   
   >> Sales table   
   >> Month        Ice cream        City                Quantity   
   >> January      Päronsplitt        Stockhomn       3   
   >> January      Top hat            Göteborg           7   
   >> January       Smugler           Malmö              13   
   >>   
   >>   
   >> Now to my question. It is important to keep historic data in the dataware   
   >> house.   
   >> So assume that I want to keep history of the price that have been   
   >> existing   
   >> on different ice cream how   
   >> would this be structured in the sales table.   
   >   
   > I don't do data warehouses, but my gut reaction would be to have a table   
   > with three columns ProductID, Date and Price.   
   >   
   > Then there is a choice whether to have a row only for the dates when the   
   > price changes or have a price for every day. The former saves space, but   
   > it   
   > makes the table more difficult to query. The latter takes up space, and   
   > you need a mechanism to copy prices from day to day.   
   >   
   > In the system I work with, we use the model with one row per day, no   
   > matter whether the price change or not, but there are two remarks:   
   >   
   > 1) We made this design with SQL 6.5 where writing a query to get the   
   > price per a certain date was difficult and not always performant.   
   > With SQL 2005 and CROSS APPLY, this is a lot easier.   
   >   
   > 2) We hold prices for financial instruments and FX rates, which typically   
   > change from day to day anyway, whereas the price for many retail   
   > products like ice cream is fairly static.   
   >   
   >   
   > --   
   > Erland Sommarskog, Stockholm, esquel@sommarskog.se   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)