Storing old and new values table structure

Question

Post reply

Storing old and new values table structure

falcon00

SSC Veteran

Points: 241
More actions
September 2, 2011 at 12:06 pm

#247374

I have always been hesitant when such requests for a new table structure would come in.
The purpose is simply to use it for a request, where user would physically specify old and new value that needs to be updated. The client does not want these values to be automated in any way, just simply for the Receiver of the request to document the change.
For example: Old Phone # -> New Phone #
The easiest way would be to create separate fields OldPhone and NewPhone, but something always tells me that it would be a great sin. Or in simple cases like these, having these types of fields in the table is acceptable?
Thanks for any suggestion.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

Have a search on this site for "Audit", "Slowly Changing Dimensions (SCD)" etc,

there are many articles, thoughts and code....hopefully this will give you a start.

For example: Old Phone # -> New Phone #

....hmmm, in the above scenario, how many changes of Phone # do you wish to keep.....just the last change, or the previous 3/5/10 changes?

good luck and post back

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

falcon00 SSC Veteran Points: 241 More actions · Answer 2

That's the thing.

If these requests went further than that by taking the new value and saving it, and keep the old value for history; then it would be no brainer.

This is is simply to complete date change request. Once the person gets the request, that record is kind of dead. It's not going to do anything else.

Another example would be a request is sent to change a department for a user.

OldDepartment -> NewDepartment

Once let's say HR gets the request, they use their own system to update the data manually. Their systems is kind of outdated. 🙂

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 3

So when do you need the old value again? I don't completely understand.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

falcon00 SSC Veteran Points: 241 More actions · Answer 4

Grant Fritchey (9/2/2011)
So when do you need the old value again? I don't completely understand.

🙂

NEVER!!!

I know I know, that's what I said.

All they want is just to send a request with Old and New Values. After that, the record is useless.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

falcon00 (9/2/2011)
I have always been hesitant when such requests for a new table structure would come in.
The purpose is simply to use it for a request, where user would physically specify old and new value that needs to be updated. The client does not want these values to be automated in any way, just simply for the Receiver of the request to document the change.
For example: Old Phone # -> New Phone #
The easiest way would be to create separate fields OldPhone and NewPhone, but something always tells me that it would be a great sin. Or in simple cases like these, having these types of fields in the table is acceptable?
Thanks for any suggestion.

If that's the case and they really don't want to keep the old value, then there's no need for a new table structure. This is all code... find the old value in the existing table and update it to the new value.

UPATE {current table}

SET PhoneNumber = {new value}

WHERE PhoneNumber = {old value}

Of course, if your company is publically traded, this is in direct violation of SOX and SEC requirements. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 6

falcon00 (9/2/2011)
Grant Fritchey (9/2/2011)
So when do you need the old value again? I don't completely understand.
🙂
NEVER!!!
I know I know, that's what I said.
All they want is just to send a request with Old and New Values. After that, the record is useless.

What Jeff said. Why store the old value at all if it's not used.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

falcon00 (9/2/2011)
Grant Fritchey (9/2/2011)
So when do you need the old value again? I don't completely understand.
🙂
NEVER!!!
I know I know, that's what I said.
All they want is just to send a request with Old and New Values. After that, the record is useless.

If they want to send a request with the Old and New Values then the old values are needed. Is that a true statement?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/