For example, say I have a table of products. Should I store logging information such as who it was created by, last edited by, last updated date, ... Or should I separate the logging information in say an auditing table if the logging information is not relevant to the actual application?
Thank you.
I would keep it separate, especially if you want to maintain an audit trail. Putting logging in with your entity means you can only have one logging entry per entity. It also mixes separate concerns - the changes to the entity and the entity itself are distinct concepts, and are best placed in separate tables.
With combined tables, deleting an entity will then delete all the audit information for that entity, which may not be what you want.
It depends on your application and how much information you are saving. Some frameworks such as Ruby on Rails can automatically update fields such as created_by, so if you have that flexibility and only need a couple of fields it might be easier for you to just keep it in the same table.
However, if you are going to log detailed information, such as who changed a record at what time, a separate table is probably better. That way you can even keep a rolling history of all the changes made to a record, for auditing purposes.
If you are serious about keeping auditing information it should go in a separate table. The last-updated stuff will just get overwritten, it's no substitute. If you add a timestamp to the key then you can keep the history in the same table, but at the expense of making queries more expensive and application logic more convoluted, so I'd advise against that.
Is the set of metatdata that you're logging about each product likely to grow? If so, put it in another table so you can add items.
If it's a concrete set that won't grow, then it probably doesn't matter. You gain a bit of conceptual elegance by using a separate table, but you give up a small amount of efficiency and complexity.
If your application only needs to know what a product's current attributes are, it's appropriate to put the audit information in another table, since it simplifies queries and improves performance.
On the other hand, if you need to be able to reconstruct entities at specific points in time (for example, if your application routinely needs to be able to answer the question "Under what brand did we sell this product in 2004?") you should never change records: the changes to the entity are part of the entity's data and should be in the same table. (See Martin Fowler's article "Patterns for things that change with time" for a good discussion of this in an object-oriented context.)
I'll usually keep LastChangeUser and LastChangeDate columns info in each table, and sometimes include CreateUser and CreateDate as well. Which is usually good for most tables.
However, if you need to store more than that, for really important tables (usually money related), go to another table. In that table (OriginalTableName_History) I usually have a HistoryID that is an auto increment, a HistoryDate, and a HistoryType (I=insert, U=update, D=delete), and then all the columns from the original table. I'll usually have a single trigger on the main table that puts every change (insert/update/delete) into the history table.
You must always separate your operational database (current info about products, customers, etc...) from logging storage. Depending on case, I also suggest you to create an "history" database, and store there all legacy data, to not overweight operational database. Doing selects on huge databases is always slow, so you must reduce it size in any possible ways and create indexes to improve performance. Logging info should be stored on some other database. Fields like "Last Modified By" I don't consider as logging info, you can have them on any table you wish. I also suggest you to not have too much foreign keys on operational database (store you log info without direct reference on operational data), because it slow down your data management too.
Hope it helps.