November 23, 2011 at 10:20 am
This is more of a general question on where everyone saves when and who created and last edited a record in the database.
My standard is to have CreateBy, CreateDate, ModifyBy, and ModifyDate on the end of each major Entity table, but is this a best practice? The only problem for me is I prefer these four columns to always be the last four columns in the table, but given adding a column in the middle of a table with a PK being referenced is not a simple task this can lead to some ARG's when the table needs to be modified.
One way I've worked around this is to split my data into two tables, so for example I'd have Customer.Master and Customer.Supplemental. Master will have CustomerID and maybe Name or some other basic information, it's main purpose is just to identify the PK for customers, but this will have my ModifyBy/Date and CreatedBy/Date columns here. Then any columns that might need to be added would go into Customer.Supplemental instead. Whether through a trigger or SP when Customer.Supplemental is updated it will update ModifyBy/Date in Customer.Master, and if a new customer is added to Customer.Master it sets ModifyBy/Date and CreatedBy/Date there and inserts a row into Customer.Supplemental with default values. Not partitioned tables per say, but they can be combined through a View when needed to bring Master and Supplemental together.
Just curious if this is a methodology used by anyone else, or if there are other suggested paths.
Thanks for any suggestions.
Sam
November 23, 2011 at 11:17 am
sam.alexander (11/23/2011)
This is more of a general question on where everyone saves when and who created and last edited a record in the database.My standard is to have CreateBy, CreateDate, ModifyBy, and ModifyDate on the end of each major Entity table, but is this a best practice? The only problem for me is I prefer these four columns to always be the last four columns in the table, but given adding a column in the middle of a table with a PK being referenced is not a simple task this can lead to some ARG's when the table needs to be modified.
One way I've worked around this is to split my data into two tables, so for example I'd have Customer.Master and Customer.Supplemental. Master will have CustomerID and maybe Name or some other basic information, it's main purpose is just to identify the PK for customers, but this will have my ModifyBy/Date and CreatedBy/Date columns here. Then any columns that might need to be added would go into Customer.Supplemental instead. Whether through a trigger or SP when Customer.Supplemental is updated it will update ModifyBy/Date in Customer.Master, and if a new customer is added to Customer.Master it sets ModifyBy/Date and CreatedBy/Date there and inserts a row into Customer.Supplemental with default values. Not partitioned tables per say, but they can be combined through a View when needed to bring Master and Supplemental together.
Just curious if this is a methodology used by anyone else, or if there are other suggested paths.
Thanks for any suggestions.
Please do not take it the wrong way but I do not think that creating new tables (which adds overhead) and one-to-one relationships (which trashes normalization) just to be sure the four audit columns are sitting at the end of the table is the most brilliant idea of this century.
Why do you care where on the table's list of columns those four audit columns end up after a while? I'm detail oriented myself and I'm also able to grasp the intrinsic beauty of having them at the end of the list but I'm not willing to affect data modeling, storage and performance just to keep them there.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.November 23, 2011 at 11:40 am
First, it doesn't really matter. If you always want those four columns at the end, specify it that way in a select statement when you query the data.
If I am building a database from scratch that requires these fields, I prefer them declared first, that way I don't have to worry about adding columns later to table and having to "move" those columns to the end.
If they are psrt of a database already in existance, I would argue against trying to keep them at the end. If needed, I would use a view that would present the data in the column order that others would prefer.
What you are really talking about here is aesthetics, how things look in a list.
If needed, I would use a view that would present the data in the column order that others would prefer.
November 23, 2011 at 11:47 am
I tend to add those columns. (period) and don't maintain them to be first or last in the list.
I don't rely on sprocs to fill out their values.
They are declared not null and default to current_date or suser_sname() and maintain the modify values using triggers.
Business may not have a direct need at initial development, but to my experience they come in very handy for debug purposes. (had multiple cases where they deleted rows and inserted new ones in stead of updating existing rows)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 24, 2011 at 12:10 am
Few thoughts:
• These are Audit Columns. Business & Business Logic should worry less on them. If you don’t want them to appear in data model (Logical) just hide them.
• The second approach that you are thinking is Trigger / SP driven. Quite unnecessary to store 2 * N tables in your data model. It perfectly makes sense to place them in regular tables.
• Lynn has a valid point that column orders in tables really doesn’t matter (subject to NULLability), it’s a SELECT statement that defines that defines the column order for user. The idea of view is brilliant; I might use it in my next model.
• I slightly disagree to ALZDBA on maintaining the audit columns with triggers. I would rather approach DEV team to fill this data (NOT NULL constraint will anyway force them to fill it) and I will make sure that SPs are populating them right with standard QA check.
In the worst scenario if you want Audit information to sit separately, I would recommend a general audit table with Table_Name, Key_Value (loosely coupled), Audit_Cols. Please note it’s a least recommended design.
November 24, 2011 at 1:37 am
Of course there columns will only get you the latest info. Intermediate modifications will not be shown at all.
You may also have a look at Tracking Changes in Your Enterprise Database by Paul S. Randal
Roy Ernest (RumblingDBA) has some nice intro articles in the pipeline to be published at SSC, but if you search SSC for RumblingDBA you can already find some nice forum threads on the topic.
btw. The reason why I use triggers to maintain the modif. columns is devs simply don't maintain these ! If they go into "cpr" modus for their app, all they focus on is getting their app fixes. Data is of secondary importance and will be violated - sorry for the term - to their needs.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 24, 2011 at 3:49 am
If all updates and inserts are done via stored procedures then the stored procedure can be written to ensure that the created/modified columns are properly updated. If inserts/updates are done directly on the tables, then I have to agree it makes since to use triggers, particularly on the updates. On an insert, defaults constraints can ensure that the insert audit columns are properly updated.
Developers may not always remember to do it themselves, especially if this is not done on every table in the database.
November 24, 2011 at 6:00 am
They may not remember and that is why it should be declared as NOT NULL.
November 24, 2011 at 12:17 pm
Dev (11/24/2011)
They may not remember and that is why it should be declared as NOT NULL.
Well, I guess we will have to agree to disagree as I believe that the modified by and modified date columns should be null until the record is actually modified. Default constraints are the proper place for populating the created by and created date columns.
November 28, 2011 at 6:21 am
Lynn Pettis (11/24/2011)
Dev (11/24/2011)
They may not remember and that is why it should be declared as NOT NULL.Well, I guess we will have to agree to disagree as I believe that the modified by and modified date columns should be null until the record is actually modified. Default constraints are the proper place for populating the created by and created date columns.
Good Point Lynn 🙂 and this is the place I happily sacrifice business meaning for column. Ideally speaking you are right but nothing stops us from forcing NOT NULL on Modified By / Date columns. In my case I populate these columns with same values as Created By / Date for the first row (initial load).
November 28, 2011 at 8:03 am
Dev (11/28/2011)
Lynn Pettis (11/24/2011)
Dev (11/24/2011)
They may not remember and that is why it should be declared as NOT NULL.Well, I guess we will have to agree to disagree as I believe that the modified by and modified date columns should be null until the record is actually modified. Default constraints are the proper place for populating the created by and created date columns.
Good Point Lynn 🙂 and this is the place I happily sacrifice business meaning for column. Ideally speaking you are right but nothing stops us from forcing NOT NULL on Modified By / Date columns. In my case I populate these columns with same values as Created By / Date for the first row (initial load).
+1
if columns have equal values, no modification has been performed.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 28, 2011 at 11:43 am
A point for keeping the modified always filled in >=created date is that your index search on "created or modified within the last 24 hours" works faster (only checking on modified date needed->efficient index). Some tables even have a version number (default 0)
January 29, 2012 at 4:12 pm
The attributes of a relation have no inherent order, because they are elements of a set (the set of attributes defining the relation), and sets have no order.
Right, now that my conscience is clear I can say that yes, it is sometimes nice to have columns like these always appear in the same position on query output, object explorer, etc.
But since columns are appended to the "end", it does seem difficult. You have to copy the table, andd the column you want, then add back your audit columns, moving all that data around. BLEGH!
Orrrrrrr..... you could just put them all at the start... 🙂
January 29, 2012 at 11:45 pm
ALZDBA (11/23/2011)
I tend to add those columns. (period) and don't maintain them to be first or last in the list.I don't rely on sprocs to fill out their values.
They are declared not null and default to current_date or suser_sname() and maintain the modify values using triggers.
Business may not have a direct need at initial development, but to my experience they come in very handy for debug purposes. (had multiple cases where they deleted rows and inserted new ones in stead of updating existing rows)
I use triggers for the same thing but I use ORIGINAL_LOGIN() just in case someone gets clever with their programming.
--Jeff Moden
January 29, 2012 at 11:46 pm
Sam, I have to agree with the others. Why all the concern over them being the last 4 columns?
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply