Update Table - Need to Know Which Update for Future Queries

[Stuugie]

Hi All,

I'm a little stuck with one table's design.

I have a table called tblConference_Board that will be updated approximately 3 to 4 times per year. The following is my table design:
CB_ID int (PK)
CB_Mnemonic_ID int (FK)
CB_Value decimal (18,10)
CB_Year int
CB_Quarter int
This table is loaded from a staging table called tblConference_Board_Staging.

When there is an update (say in the spring), the values will either be in quarters or annual values. The year range has been consistently the same and usually starts from 1961 to 2035. When the next update comes (in the summer), the yearly range will be the same but the actual values will change. What I'm stuck on is how to determine which data set is from which update? I thought that another field like maybe CB_Modified date which would populate with the date that update takes place would be a good idea but that way may be tricky to query after the updates.

It's hard for me to summarize my question but it should be something like: How do I populate my table and know which values represent which update? I'm really hoping someone can make sense of my confusion and if you require more information please ask.

[Reverend Jim]

If you are doing an UPDATE rather than an INSERT then you will be overwriting existing values so tagging the records with the date of the update won't help. How about the following structure

Update_Date (PK)
CB_ID int (PK)
CB_Mnemonic_ID int (FK)
CB_Value decimal (18,10)
CB_Year int
CB_Quarter int

and make the primary key a compound key consisting of the update date and CBID. That way you retain all historical data. This is what I did with the Dovercourt EMS analog point data. We had to maintain the historic values. At 8000+ points every hour the data does tend to pile up (around 400 meg per month).

[Stuugie]

Sorry, I meant inserting not updating. I had added a Date_Modified field but didn't make it a PK, is that necessary?

[Stuugie]

Well I just completed an ETL process with SSIS as well as I put in some dummy data with a different "date modified" (for 2012-12-01) and then ran a query like:

select * 
from dbo.tblConference_Board 
inner join 
dbo.tblConferenc_Board_Mnemonic_List 
on tblConference_Board.CB_Mnemonic_ID=tblConferenc_Board_Mnemonic_List.cbq_Mnemonic_ID
and CB_Value<>0 and CB_Quarter=0 and cbq_Date_Modified between '2013-02-01' and '2013-05-01'

and the result was just as I intended. Thanks!

[Reverend Jim]

I had added a Date_Modified field but didn't make it a PK, is that necessary?

It depends on

1. how you want to access the data
2. whether you will get PK violations on CB_ID if you don't