DB fundamental

[theJonster]

Bit of a big question, hoping to start a discussion or just get the best answer out straight.

Trying to work out which is the best way to database history of tables and who did updates to a record.

You have a table:

MEMBERS
MemberId
Name
LName
Email
Password

Now the member updates their details and you dont want to lose their history.

I can think of 2 options:

1.

Create a history table as such.

MEMBERSHISTORY
MemberHistoryId
MemberId
Name
LName
Email
Password


So now you snapshot any changes made to members into this table.
But this seems a bit over the top if the user only updates say one field to snap shot a whole record.


2. The other way I see is:

You normalise the table further, and use a bit Active field to identify the latest details. Thus when a member changes thier details it simply inserts new ones into those tables.

MEMBERS
MemberId

MEMBERNAMES
MemberNameId
MemberId
Name
LName
Active

MEMBERLOGINS
MemberLoginId
MemberId
Email
Password
Active

Which is the better way to do this? What is the standard procedure that is used generally? Type 1 is easier to make, is type 2 a better way to database?


The second question involves capturing who made an update.

Say you have MEMBERS but also ADMINS who can update MEMBERS.

I could add to All Tables

AddedBy varchar
DateAdded
UpdatedBy varchar
DateUpdated

Now if an update is made i can put the name of the admin in there if updated by an admin, or put in the name of the member if updated by the member. And capture that date that the record was added or last updated.

What is the most effective way to do this using IDs?

So an admin updates the table then his ID must be attached to that update, or a member does the update then his ID is attached to the update.

I have an idea as such:

Say updating MEMBERS

MEMBERNAMEMEMBERUPDATES
Id
MemberNameId
MemberId

MEMBERNAMEADMINUPDATES
Id
MemberNameId
AdminId

But this means creating a table for type of user that can update a table for every table that can be updated...this can't be right.


I might be over complicating the whole thing but any pointers would be appreciated.

Thanks

[ck9663]

This is what I would've done it.

1. Create your table.
2. Include these columns:

• RecordStatus (Bit)
  InsertedBy (varchar())
  InsertedOn (Date)
  UpdatedBy (varchar())
  UpdatedOn

3. Insert new record for new users.
4. For updates, terminate the old record and insert the new one. Terminate means you update the RecordStatus to mean that it's a dead record and will be filtered out in WHERE clause whenever someone access this table (or you can create a view or table-function).
5. For the InsertedBy, InsertedOn, UpdatedBy, UpdatedOn, use DEFAULT values.
6. To ensure that everything is paired (new record and old record) use TRIGGER.

Happy coding.

-- CK

[theJonster]

Hi

Thanks for the response. What you suggested is how I am also doing it currently. I just thought there may be some way to link updates to specific Primary Keys instead of just have a varchar field with say a persons name in it.

[ck9663]

There's also a concept that keep this columns:

PrimaryKey
NameofUpdatedColumn
OldValue
NewValue

This would mean you have to keep this table every time there's an updated. So that on that time, you can monitor the movement of the data. For ease of analysis, I still recommend what you're currently doing.

-- CK

[Delerna]

From the way you describe your second question, thats what users and permissions are intended for. You assign users to your database objects as well as the kind of actions that they can perform (update,delete,read,insert,execute)


In order to log who is making a change you can then use something like

Select Loginame From MASTER.DBO.sysprocesses where SPID=@@SPID

within your stored procs/views/UDF's to determine who it is that is executing it.
This means, of couse that each person must have their own login, at least for the ones you want to log, at least.

I use intranet reports and pass the login from page to page where this is important, otherwise they would need to login to every page where tracking users is important.