SQL Server Database Diagram, Entity Framework and CreatedBy/UpdatedBy fields

Question

User1738843376 posted

Hi,

I'm creating a database diagram on by SQL Server DB in order to connect the DB to my Website in WebForms via Entity Framework.

I usually don't use EF, i also usually overlook the database diagram when creating the DB for various reasons, which include the usual ongoing process of accommodating last minute changes to the structure by request of the client, so i'm a bit of a rookie on this matter.

My usual tables include a CreatedBy and UpdatedBy fields, where i store the uniqueidentifier referencing the user from the User table.

Should I be setting all these relations on the Database Diagram? Also, how do i set the relation of the user to itself, since the record might be created/edited by another user, so the User table also includes the same fields.

Or should i be removing all these from the table, and create an audit table to record all interactions on the table, for each existing table? Seems like overkill for the actual requirements...

Maybe i should overlook these relations?

Please advise what you think is the best way to go at it

Answer 1

User1535942433 posted

Hi 0belix,

My usual tables include a CreatedBy and UpdatedBy fields, where i store the uniqueidentifier referencing the user from the User table.

Should I be setting all these relations on the Database Diagram? Also, how do i set the relation of the user to itself, since the record might be created/edited by another user, so the User table also includes the same fields.

Accroding to your description,I don't understand your requirment clearly.Could you tell us more details of your requirments to us?

1.What is the specific application scenario?

2.One table is User table,what tables do you have?

3.What is sentence of "since the record might be created/edited by another user"?Do you have users or adminitors?

4.What the relations do you want to have?

Best regards,

Yijing Sun

Answer 2

User379720387 posted

When you have the relationships defined (via the diagram for instance) then the true power of EF comes into play.

Issues with the structure of your data are easier to see when you have diagrams.

In my experience an audit table quickly becomes a project in itself.

Here are a few consideration that will illustrate this:

in addition to  capturing the userId, you would need a timestamp as well, right?

then wouldn't you need to capture if something was deleted, added, or modified?

then are you going to capture what the change was (before and after), for how many variables?

etc.

Unless the business requirements dictate different, go with a minimal viable implementation

Answer 3

User1738843376 posted

Thanks for your reply wavemaster,

I acknowledge your considerations regarding extensive auditing resulting in a project of itself, and i do not need such extensive data, but i usually do need to record who last edited a particular row, apart from who created the same row, both of which are followed by timestamp fields.

These leaves me in nowhere land as far as your answer goes... 

Having the said requirements, and having also into account the fact that a record from the User table might be edited by a different user with superior privileges, how should i reference this private/foreign keys in the same table regarding the SQL diagram? Is there no in between solution that does not require me creating an auditing table so that the relations might work on the diagram and subsequently on the EF model? Or should i simply forget those relations in the diagram, keep the fields, and not have the EF model relate the creator and last editor user of each row as a User model? This last option will leave me with the need of manually binding the User model with itself only upon request, so that i do not create an infinite succession of requests...

Do you see any other options?

Answer 4

User1738843376 posted

Hi yij sun, 

Thanks for your reply!

There are no particular requirements for a specific project, i'm speaking generically.

In sum, any table in my databases needs its records to save which user created it, at which point in time, and also who lasted edited the record, and again, the datetime of the last update.

Also, regarding your question about the users/admins, each user can have a multiplicity of roles. For instance, a user can be a buyer, and at the same time a content admin on the site, or any other example you can think of, so there usually is a User table, a Role table, both related by a UserRole table.

A User record, can be edited not only by the user itself, but also by any user having a role with such permissions. Having said this, i need to record that same info of who created the user (might not be the user itself, but an admin, or any other user carrying a role that would allow it), and who edited it last.

This leads to my initial post questions... how to i transfer this to an SQL Diagram so that EF can work from there? Should i simply forget about these relations being mimicked to the models, and request the information manually when needed so that i do not create an infinite queue or requests?

Adds that i do not need the overhaul of audit tables to carry the trace of every update of every row in every table, just the information i mentioned earlier, so i'd like to keep away from creating a full auditing project, as mentioned by wavemaster in another reply to my post.

Answer 5

User379720387 posted

Let's say that recordId represents a single record.

new table WTHH

Id, recordId, timeStamp, User, ChangeString

Everytime an update operation happens you create some sort of string that captures what was changed.

Later on you can navigate from Records to WTHH to find all changes that were made.

Answer 6

User475983607 posted

Typically I do not set a foreign key constraint CreatedBy and UpdatedBy columns.  In my experience the only time you care about the fields is when there is a data problem and you are trying to figure out what happened.  

It is also common to have audit tables.  Usually these are used to store the state of an entire record so you have a history of what fields changed and when.

Please advise what you think is the best way to go at it

It's hard to say without having a full understanding of the requirements.