By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,727 Members | 757 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,727 IT Pros & Developers. It's quick & easy.

Record Snapshots

P: n/a
Greetings all, please bare with me this will take a little bit to explain
but I'm really hoping someone might have some ideas or can point in the
right direction. I'm trying to create "snapshots" of data that belong to a
profile. I want a user to be able to look at a profile and see the most
recent rounds of data or, select a previous snapshot and view that snapshots
data. The current snapshot data can be changed and tweaked until the user
is happy with it and then he can take a snap shot. Now i've got something
that works using the following tables.

CREATE TABLE Profile
(
ID INT IDENTITY PRIMARY KEY,
Name VARCHAR(50)
)

CREATE TABLE Profile_SnapShots
(
ID INT IDENTITY PRIMARY KEY,
Profile_ID INT
)

CREATE TABLE Profile_Data
(
ID INT IDENTITY PRIMARY KEY,
Profile_ID INT,
Street_Address VARCHAR(50),
Postal_Code VARCHAR(50)
SnapShot_ID INT
)

So, to take a snap shot I do the following.

-- ################################################## #######
CREATE PROCEDURE Take_SnapShot
@profileID INT,
@activeSnapShotID INT
AS

DECLARE @newSnapShotID INT
INSERT INTO Profile_SnapShots (Profile_ID) VALUES (@profileID)
SET @newSnapShotID = @@IDENTITY

INSERT INTO Profile_Data (Profile_ID, Street_Address, Postal_Code,
SnapShot_ID)
SELECT Profile_ID, Street, Address, Postal_Code, @newSnapShotID
FROM Profile_Data
WHERE Profile_ID = @profileID
AND SnapShot_ID = @activeSnapShotID
-- ################################################## ########

This works well as long as i don't have any tables that relate the ID of any
of my tables because as soon as I insert and start referencing the new row
I'm of course not using the same ID anymore. This is where I'm stuck.

Here are examples of two additional tables.

CREATE TABLE Profile_Funding
(
ID INT IDENTITY PRIMARY KEY,
Profile_ID INT,
Funding_Round VARCHAR(50),
Funding_Amount INT,
SnapShot_ID INT
)

CREATE TABLE Profile_Funding_Participants
(
ID INT IDENTITY PRIMARY KEY,
Funding_ID INT,
Funder_Name VARCHAR(50),
Funder_Type VARCHAR(50)
)

Now if my snap shot stored procedure looks like this.

-- ################################################## #######
CREATE PROCEDURE Take_SnapShot
@profileID INT,
@activeSnapShotID INT
AS

DECLARE @newSnapShotID INT
INSERT INTO Profile_SnapShots (Profile_ID) VALUES (@profileID)
SET @newSnapShotID = @@IDENTITY

INSERT INTO Profile_Data (Profile_ID, Street_Address, Postal_Code,
SnapShot_ID)
SELECT Profile_ID, Street, Address, Postal_Code, @newSnapShotID
FROM Profile_Data
WHERE Profile_ID = @profileID
AND SnapShot_ID = @activeSnapShotID

INSERT INTO Profile_Funding (Profile_ID, Funding_Amount, SnapShot_ID)
SELECT Profile_ID, Funding_Amount, @newSnapShotID
FROM Profile_Funding
WHERE Profile_ID = @profileID
AND SnapShot_ID = @activeSnapShotID
-- ################################################## ########

Then any tables relationships built via the Profile_Funding.ID column are
now invalid. Especially since i want to be able to delete past snapshots.
Additionally the funding table could have x numbers of funding rounds for
the current snap shot for each profile, and each round could have a number
of funding participants.

Any thoughts on a better way to take snapshots? Any thoughts on how to
solve my problem with the participants table?

Thanks,
Zach
Jul 20 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.