Is it a good practice to map UserAccount Table with all other tables in SQL Server?

Question

I have UserAccount table and other tables like Employee, Student etc. I want to have an audit like who created a student record or who created a certain employee record. Is it a good practice to have UserAccountId as foreign key in all other tables like Employee, Student etc? I am using hibernate if I mapped like this I have to maintain one to many relationship between UserAccount and All other Classes so code increases and for me that is a burden.

Answer 1

Well it breaks all normalisation rules. Have a link/href table instead. UserAccountID, EmployeeID(NULL), StudentID(NULL). Have one massive linked table like this. The foreign Keys needs to be nullable besides UserAccountID(Primary Key and Foreign Key).

Answer 2

"Good habit/practice" is subjective.

If the business domain includes the fact that the person who created an entity is a meaningful piece of information, and that this is likely to be a regular request by end users, then adding a "createdBy" attribute to your tables/classes is, indeed, good practice.

The best way to know whether this is true is to ask the product owner whether they would need a screen showing "all employees created by user x". If they say "no, only if something goes wrong", you have an audit requirement; if they say "yes, we'll use that regularly", it's an integral part of your business domain.

You may find, that your users want to know not just who created a row, but also who modified it. In that case, there are similar questions on SO.