I'm using the subtype/supertype model and have some basic audit fields (created by, created date, modified by, modified date). Question is, do these fields need to exist at the subtype level tables as well, or is it sufficient to only have these at the top-level table, and any time an update is made to the subtype tables, simply trigger an update to the supertype.

Thanks in advance,

-A

that sounds like a great idea

both ways

or either

Looking for a serious reply; I know it might be too much to ask

i was being serious, just concise

it depends on what you need

if only the main supertype attributes need to be tracked, then don't put the audit columns in the subtype tables

if you have attributes in the subtype tables that need to be tracked, then you would put the audit columns in there -- possibly triggering a subtype attribute change up to the supertype, possibly not

you revealed nothing about your particular supertype/subtype structure, and nothing about your particular auditing needs

so of course the serious reply is: "it depends"

I would not update the audit fields on the parent records just because a child record is modified.

I'd actually agree with Rudy wholeheartedly. Either, both, or neither as you need them.

The idea of "tagging" rows with audit metadata like creation date/user and update date/user exists outside of conventional modeling. These attributes don't contribute or detract from the basic model, and they aren't used by the basic model. They exist only to serve whatever auditing needs you've got, so include or exclude them as your needs dictate.

The way that I perfer to handle this is via an audit table. When auditing, I like a trigger to copy the "interesting" columns to an audit table along with the user information, machine information (IP address and name if that is available), and datetime. The initial insert of data leaves a completely NULL audit row, so I get the creation information at no charge and in a consistent format. This also gives me the ability to see who changed what when, and to even roll that back if necessary.

-PatP

In this instance, the tables store ad campaign information, and based on the campaign type, a different sub-type table is used. A campaign can only belong to one campaign type. So in this case, my thinking was why repeat the audit fields in every detail level table since the sub-types are logical extensions of the super-type.

Thoughts?

-A

If any change to a subtype implies a change to its supertype, and your auditing doesn't care about changes to the subtype, then your case for only auditing the supertype makes sense. This is very, VERY rare, but it could happen.

In most cases, auditors want to know who changed what, when, and often want to know what the previous value was too.

-PatP

Exactly. The way I see it, we either implement full auditing where we know the previous values; new values, etc. or, we simply put these basic audit fields at the super-type level. I don't think repeating these audit fields in each sub-type adds much value.

-A

Even if the Audit fields are either on the supertype or on the subtypes tables, or if you add only one Audit field set to only one of them, I'm thinking now how can you retrieve this kind of inf?

Assuming you want to keep a history of your previous records (otherwise the Audit fields wll hold only the last change whitch doesn't make much sense) , how can you combine the archive tables for the supertype and subtype to have a full image of the whole record(supertype+subtype) that was at one point valid.

I couldn't find any solution except for all the time in the two history tables, whenever there is a change in one of them, you make a copy of the other one as well and link them some-how.

I hope I'm not complicating things more, so please correct me if I'm wrong.