Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!feeder.erje.net!eu.feeder.erje.net!eternal-september.org!feeder.eternal-september.org!mx02.eternal-september.org!.POSTED!not-for-mail From: "Tony Johansson" Newsgroups: comp.databases.ms-sqlserver Subject: Re: How to keep a history of price for different ice cream in a dataware house Date: Sun, 15 Feb 2015 21:38:19 +0100 Organization: A noiseless patient Spider Lines: 51 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; format=flowed; charset="Windows-1252"; reply-type=original Content-Transfer-Encoding: 8bit Injection-Date: Sun, 15 Feb 2015 20:38:00 +0000 (UTC) Injection-Info: mx02.eternal-september.org; posting-host="8fdc2b5af81c338deb7045fc6c39b0e2"; logging-data="24332"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1+pUVaAXs//X92xWotATUK6" X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18463 In-Reply-To: X-Newsreader: Microsoft Windows Mail 6.0.6002.18197 Cancel-Lock: sha1:Z8pu/UhGrtP0U89NQz2QGOzWTcM= X-Priority: 3 X-MSMail-Priority: Normal Xref: csiph.com comp.databases.ms-sqlserver:1890 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