Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Table Examples

Author  Topic 

SouthSideRob
Starting Member

22 Posts

Posted - 2014-03-28 : 12:00:21
I'm trying to determine how to create a table that will track reporting errors by the users of my application. Now, the person that has disputed (spotted the error) can enter comments as can that person's supervisor and that person's program manager so I need 3 fields for comments, plus, I want to insert the user's ID and datetime when the record was disupted plus any changes made to the record so I was thinking to have a CreateBy and CreateDt field for the user. A LastUpdateBy and LastUpdateDt for the user. My problem is, I want to also track the supervisor and program manager both when creating or altering their comments. I'm trying to browse the net to see a simaliar table example that I can use to help me with the design. Any suggestions ???

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-03-28 : 18:45:42
Why not add the SuperviserCreateDate, SupervisorModifyDate, PgmMgrCreateDate and PgmMgrModifyDate columns to your table. They would initially be Null or an acceptable constant (e.g. 1900-01-01). You could then either trust your INSERT and UPDATE logic to set these columns appropriately or impose a trigger that would detect any changes to the appropriate Comment fields and set the relevant dates.

===============================================================================
“Everyone wants a better life: very few of us want to be better people.”
-Alain de Botton
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-31 : 11:27:57
You might be better off doing a structure something like a forum. Then you an have as many comments/replies as you need. That way, if your requirements change, you won't have to add columns to a table.
Go to Top of Page
   

- Advertisement -