472,328 Members | 1,072 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,328 software developers and data experts.

Tracking changes

I have asked this here before, but am still trying to decide what's best,
and would appreciate the input of seasoned Asp developers.

It's an ASP-built Intranet app, so I can't show you the site, but I'll show
you screen shots if you think that will help. The app is to keep track of
issues called in to a Support help desk. These issues are called tickets.
Each ticket has one and only one row in this given table (let's call it
Ticket). Any additional info on a ticket will be entered into another table
(called History), so it's possible to have many rows in that table for each
one in the table I am focusing on.

The app in question uses SQL Server 2000 as its datasource.
The table contains info entered when a ticket was created, but I have
included a mechanism for users to go back and change info that was
originally entered. This leads me to my dillemma. The boss wants me to find
a way to keep up with changes to that original info. It seems logical to
keep this info in another table. Let's call this new table Audit.

I have explored using a trigger which would create a row in the Audit table
whenever an Update was used on the Ticket table. The problem with that is,
you have one row per changed field, and that could add up. Actually, it's
not a problem, just something to consider.

The alternative is to have ASP code which would, when any edits are made,
manually compare each new value in the form to each existing value, and
record those items which have changed. This seems like it would put a lot of
work on the web server. There are generally maybe 10 people using this app
at any given time, so that may or may not be important.
For the record, the Ticket table has about 40 fields. The PK is a single
field, and it's an Identity with type int. Most of the other fields are int,
as they contain FKs that point to values contained in statics tables. There
are a few tables of type varchar, and 3 fields which contain large amounts
of data, as they are description fields. Users type in up to 3000 characters
here, but it's typically a few hundred characters.

Besides keeping track of this stuff, I am going to have to consider that I
will have to display this stuff, as well.

I'd appreciate the input of those here who might have encountered such a
thing.
Aug 28 '06 #1
4 1416
Middletree wrote:
The app in question uses SQL Server 2000 as its datasource.
The table contains info entered when a ticket was created, but
I have included a mechanism for users to go back and change
info that was originally entered. This leads me to my dillemma.
The boss wants me to find a way to keep up with changes to that
original info. It seems logical to keep this info in another
table.
I don't know what your data looks like or how frequently records get
changed, but one option is to retain complete revisions of each record.
Except for your three large text fields, it does not sound like each record
is especially large.

You could even opt for a hybrid approach -- keep a complete revision for
each field EXCEPT those three fields, storing pointers to those text fields
and using comparisons to determine if those text fields require new records.

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.
Aug 29 '06 #2

Middletree wrote:
I have asked this here before, but am still trying to decide what's best,
and would appreciate the input of seasoned Asp developers.

It's an ASP-built Intranet app, so I can't show you the site, but I'll show
you screen shots if you think that will help. The app is to keep track of
issues called in to a Support help desk. These issues are called tickets.
Each ticket has one and only one row in this given table (let's call it
Ticket). Any additional info on a ticket will be entered into another table
(called History), so it's possible to have many rows in that table for each
one in the table I am focusing on.

The app in question uses SQL Server 2000 as its datasource.
The table contains info entered when a ticket was created, but I have
included a mechanism for users to go back and change info that was
originally entered. This leads me to my dillemma. The boss wants me to find
a way to keep up with changes to that original info. It seems logical to
keep this info in another table. Let's call this new table Audit.
So you're keeping track of changes to the parent table? What kind of
changes can happen? Have you considered locking down certain fields
depending on the status of the ticket?

How many tickets a day are generated? How many times is a typical
ticket changed?

Aug 29 '06 #3
So you're keeping track of changes to the parent table?

Yes
>What kind of
changes can happen?

Mostly varchar (less than 50 characters) or int fields (FK to another
table). Specifically, these would be changes to the data that was originally
entered. A customer calls in and says this problem is occuring at several
branches, so we check the box that says "multiple branches", then as we work
through the problem, we find that it only affect one branch. Stuff like
that.

Have you considered locking down certain fields
depending on the status of the ticket?
We want to leave open the possibility to change this info.
How many tickets a day are generated?
Usually less than 10.

How many times is a typical ticket changed?
Usually not at all. But when it does, we want to have a record of it.
Aug 29 '06 #4

middletree wrote:
So you're keeping track of changes to the parent table?

Yes
What kind of
changes can happen?


Mostly varchar (less than 50 characters) or int fields (FK to another
table). Specifically, these would be changes to the data that was originally
entered. A customer calls in and says this problem is occuring at several
branches, so we check the box that says "multiple branches", then as we work
through the problem, we find that it only affect one branch. Stuff like
that.

Have you considered locking down certain fields
depending on the status of the ticket?

We want to leave open the possibility to change this info.
How many tickets a day are generated?

Usually less than 10.

How many times is a typical ticket changed?

Usually not at all. But when it does, we want to have a record of it.
FWIW, I'd just make another entry an identical table that keys back to
the original.

Aug 30 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Steve Mew | last post by:
Has anyone come across a change tracking tool for SQL Server. Specifically the scenario I want is the following : A Production DB needs some...
2
by: Peter Wright | last post by:
Hi all. Hopefully this should demonstrate the problem I'm having: http://flooble.net/~pete/focus-problem-demo/ (I'm testing it in Mozilla...
4
by: Tim Graichen | last post by:
Does anybody have code for real geneology tracking through many generations? And maybe an associated family tree report? Tim
1
by: Muddassir | last post by:
hi everybody I am writing an application for tracking files and directory changes I used FindFirstChangeNotification...
4
by: Glenn Owens | last post by:
I have a DataGrid web control which I've dynamically populated with template columns to be used for bulk-editting. Generally, all of the columns...
1
by: fred tate via .NET 247 | last post by:
I'm working on a project that will track a great deal of data forindividuals and will keep track of users for a very long time (5- 10) years. I'm...
2
by: anony | last post by:
Maybe this feature is already out there. I guess you could write triggers to do some of this. Often when designing a database I add a start_date...
4
by: YYZ | last post by:
Just wondering if there is a good way to do this that won't take me forever and a day. My form has many textboxes and comboboxes and radio...
4
by: tagg3rx | last post by:
Hi all, hoping someone can point me towards a version tracking system for .net development. My projects are starting to get kind of large and my...
2
by: sparks | last post by:
At first they just wanted to keep a record of who logged in and when. Then it was if they made changes. Now its who changed what. BUT since this...
0
by: tammygombez | last post by:
Hey everyone! I've been researching gaming laptops lately, and I must say, they can get pretty expensive. However, I've come across some great...
0
better678
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
0
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
0
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
0
by: CD Tom | last post by:
This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
1
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.