May 24, 2016 at 12:07 pm
Some time ago I asked a question here about logging changes to the data as users use our app. I've got some feedback, but one thing I couldn't answer at the time was what our tables were going to look like. I've now got that information. My boss has made an ER diagram using (I think) something called DTM Data Generator. Its a tool I'm not familiar with and the ER diagrams use some notation I'm also not familiar with. I'd love to take a screen shot or use the Windows Snipping tool, but I've never really had success pasting images to the SSC forums, so I'm sorry but you'll have to put up with my trying to copy the information down and typing it in here. Since the ER diagram is closer to a UML diagram, rather than a DDL statement, it may be difficult for some. And like I said there's some notation here which I've never seen before. For example I couldn't figure out if any of the columns were nullable or not, as nothing told me if anything was nullable or not. But I just learned that this tool uses the notation of "NN" to mean "Not Null", so well I've got something. Anyway, here goes the first table, which is the parent table. It's named User_Action:
Primary table: User_Action
UA_ID BigInt PK NN
AppID varchar(10) NN
UserID varchar(1) NN
IP_Address varchar(15) NN
TableName varchar(40) NN
ActionDateTime datetime NN
ActionTaken varchar(6) NN
RecordsReturned BigInt
tSQL text
And next is the only child table, it is named User_Update:
Child table: User_Update
UA_ID BigInt PK and FK NN
ColumnName varchar(40) NN
OldValue varchar(255)
NewValue varchar(255)
OldText text
NewText text
OldBinary binary(8000)
NewBinary binary(8000)
The ER diagram indicates that there's a one-to-many relationship between User_Action and User_Update. I don't see how, as User_Update only has 1 column which is the primary key and foreign key and since it appears to me as though ColumnName would likely be there for multiple UA_ID, I think we've got a problem, if more than 1 column gets updated at once. Or am I mistaken?
The next problem I see if that User_Update is greater in width than 8192 bytes, which the last time I knew, was the widest any SQL Server table could get. Am I correct about that? My training on that does date back to SQL Server 2000, so I could be wrong now.
Third problem, I don't know how to get all of that information from entity framework, because I've been told not to use UPDATE or DELETE triggers. Does anyone here have any idea how to get all of that from EF?
Kindest Regards, Rod Connect with me on LinkedIn.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply