November 25, 2003 at 7:58 am
I have a row in a table containing, amongst other data, a number for an item for the current month. I also need to keep a history of the value for each month which will be in a separate table. I will get an update file every month for items which have changed so if there is no change in the number of items I do not get a row in the update file.
The question is whether I should store in the history table only the values which have changed or create rows for missing data?
The second option would appear to better from a performance point of view because to get the data for any month I can query the table and return rows for the that specific month. I can get the data from the first option using a subquery or similar to return the rows for max(month) where month is less that or equal to the required month.
The volume of data is not huge (tens of thousands or rows) so restricting the amount of space used is not a primary concern. What is more of a concern is the performance of the query to get the data for a specific month.
From other data in the system I can find out which items are missing and create them quite easily.
Can anyone give me some guidance as to which option I should choose?
Thanks
Jeremy
November 25, 2003 at 11:34 am
I would use the First option with a "Numbers" table if you join that with your history the non row moth will came as Null and as you can see there should be a performance gain when you are querying for changes and you don't have NULLs stored!
HTH
* Noel
November 26, 2003 at 1:27 am
I'm inclined to agree that just storing the data I have is the better option than creating data.
The values I might have created would be the current value not the change in value. If an item had a value of 5 last month and is still 5 this month I would not get a row in the update file but I might have created a row with a value of 5 for the current month.
Thanks
Jeremy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply