Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1890

Re: How to keep a history of price for different ice cream in a dataware house

Show all headers | View raw

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" <esquel@sommarskog.se> 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 

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

How to keep a history of price for different ice cream in a dataware house "Tony Johansson" <johansson.andersson@telia.com> - 2015-02-15 18:02 +0100
  Re: How to keep a history of price for different ice cream in a dataware house Erland Sommarskog <esquel@sommarskog.se> - 2015-02-15 19:58 +0100
    Re: How to keep a history of price for different ice cream in a dataware house "Tony Johansson" <johansson.andersson@telia.com> - 2015-02-15 21:38 +0100
      Re: How to keep a history of price for different ice cream in a dataware house Erland Sommarskog <esquel@sommarskog.se> - 2015-02-15 22:14 +0100
      Re: How to keep a history of price for different ice cream in a dataware house "Tony Johansson" <johansson.andersson@telia.com> - 2015-02-15 22:30 +0100
  Re: How to keep a history of price for different ice cream in a dataware house rja.carnegie@gmail.com - 2015-02-16 03:51 -0800
  Re: How to keep a history of price for different ice cream in a dataware house --CELKO-- <jcelko212@earthlink.net> - 2015-02-16 08:37 -0800
  Re: How to keep a history of price for different ice cream in a dataware house gposta10@gmail.com - 2015-03-23 11:44 -0700

csiph-web