0

What is the appropriate column(s) type(s) (trying to be database agnostic, but realize that could play into the answer) to store a months worth of pricing data (i.e. could be the daily hotel rate, etc.) as a snapshot for every day of the month?

The use case is to collect pricing data on a daily basis for a given month. For example, on February 1st, collect 28 (2/1 - 2/28) days worth of data, then on February 2nd, collect 27 (2/2 - 2/28) days worth of data,...,on February 28 collect 1 (2/28) day worth of data.

The reason for continually collecting the pricing data is that prices often change and given the example above so that historical pricing data is recorded (as opposed to just rewriting the pricing data for a given month).

Improve this question
2

For a relational database the correct way is to have a normalized table:

hotel_id         <whatever type>,
collection_date  date,
price_date       date,
price            numeric()

It sounds like you want to "compress" the price values into some sort of array. There are relational database systems which support the array type, PostgreSQL for one. Document stores (Mongo et al) have arrays as a natural part of their JSON support. Time series databases are optimized to store a changing metric (the room price) over time, by key (the hotel ID).

Many relational databases support compression and column-wise storage (Vertica, Teradata et al). These will space-optimize the storage of the values that are shared by many rows. This optimization is transparent to the application, which sends and receives rows oblivious to the DBMS's internal processing.

Improve this answer
8
  • Yes, an array was what I had originally thought as an acceptable column type. But the thought of having ~30 arrays (of diminishing size) sounded troublesome. Querying the arrays in order to compute aggregates (i.e. revenue per month, etc.) didn't seem efficient either. I could under stand using the array if that was the only storage for price information as it was continually updated. Again, that doesn't sound efficient and given the relatively low cost of storage these days, seems logical to store for all collection dates. – user2715877 Feb 7 '19 at 14:23
  • Utilizing the above table format, how would you structure the query to return the day (price_date) and the price seeing how the last day of the month would have N (where N = number of days in the month) rows if wanting to look at a previous full months data? – user2715877 Feb 8 '19 at 4:25
  • In your question you said the last day of the month would have only one value not N. – Michael Green Feb 8 '19 at 9:26
  • I did, incorrectly. Looking to find SELECT price_date, price that would return the most current price for each price_date. For example, if it were February 1st and I wanted to know what the final price was for each price_date in January. – user2715877 Feb 8 '19 at 15:24
  • I'm confused. Are you asking in advance what the price would be if you were to book a room for some future date, or recording in arrears what was actually charged to guests in the past? Or tracking price for historical combinations of booking date & check-in date? – Michael Green Feb 10 '19 at 4:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.