[ss5416]

Warning---I am brand new to these forums but plan to return often. Apologies in advance for making my first post a question.

I have been working on a model for a while now that has a piece that I'm not quite comfortable with. I'd like to know if the concept I am attempting is breaking a fundamental rule. Honestly, it does not look right.

I am representing several Objects in my DB. For example, Forms which contain Sections which contain Questions. Imagine the 5 tables required for that. Now, I want to record Events on these objects, for instance, a Question is responded to, a Form property (say title) is modified, a Section is added. There are other objects but I am trying to be brief.

And now the question:
Does it make sense to have an Objects table that assigns a key to each object:
Object_Type .. Object_Name
1................... Form
2................... Section
3................... Question

So that I can record Events like such:
ID .. Object_Type .. Object_ID .. Event_Type .. UserID .. Timestamp
1.... 3 ................... 927 ........... Response ..... ss5416 ... etc ...
2.... 1 ................... 12 ............. Modification .. ss5416 ... etc ...
3.... 2 ................... 41 ............. Addition ....... ss5416 ... etc ...

Where the Object_ID relates back to the appropriate table? 927 being the Questions PK, 12 being the Forms PK, etc.

This just doesn't feel right because I can't make the relational concept work even though it is a valid OO concept.

If this is a common problem, perhaps a link to a reference that discusses it.

I appreciate your interest...

[blindman]

You gain no advantage by assigning a key code to the objects. You'd just have to maintain the table of key codes. Why add administrative overhead for auditing? If you really want to, you could use the code for the object from the Access system tables.

When I want to add event auditing to my applications, I just have the object (form, report, whatever...) record its own activity in an audit table, and it refers to itself by its own name. You could even use the NAME property in access.

[sbaru]

This is how I would do it:

Object (ObjectId, ObjectTypeId, ParentObjectId)
ObjectType(ObjectTypeId, ObjectTypeName)
Attributes(AttributeId, AttributeName)
ObjectTypeAttribute(ObjectTypeAttributeId, ObjectTypeId, AttributeId)
Data(ObjectTypeAttributeId, EventId, Data, TimeStamp, UserId)

This model would be generic enough so that even if the attributes for a ObjectType(Page, Section, Question etc) change, you should be fine. And all the objects irrespective of the type will go into the same table.

[ss5416]

Originally posted by blindman
You gain no advantage by assigning a key code to the objects. You'd just have to maintain the table of key codes. Why add administrative overhead for auditing? If you really want to, you could use the code for the object from the Access system tables.

I am not using Access but I understand where you are going. It is just broken out as a definition table for ... well, for no good reason, I guess. Perhaps I'll take you up on that. Just a lot more efficient storing a 4 Byte key then a varchar(20) or so...

Originally posted by blindman When I want to add event auditing to my applications, I just have the object (form, report, whatever...) record its own activity in an audit table, and it refers to itself by its own name. You could even use the NAME property in access.

I considered this as well. If I understand you correct, my Form object table would have its own auditing table etc. I considered that but started worrying about accumulating data from so many sources. For instance, imagine a report that showed all of the events triggered by me. It would have to query (and union I suppose) every object audit table.

Imagine...
Forms (Form_ID, Form_Title, ... add'l properties)
Form_Events (Form_ID, Event_ID, User_ID, TimeStamp, ...)

Then duplicating that structure for every object. Gets overwhelming. I think the 2nd poster may be onto something... He basically says, define an objects table to replace the Forms, Sections, Questions, etc. Then specify their specific related properties. I like that. I'll post a reply to that but please reply to this thread if anyone sees flaws with things discussed here.

[ss5416]

Originally posted by sbaru
This is how I would do it:

Object (ObjectId, ObjectTypeId, ParentObjectId)
ObjectType(ObjectTypeId, ObjectTypeName)
Attributes(AttributeId, AttributeName)
ObjectTypeAttribute(ObjectTypeAttributeId, ObjectTypeId, AttributeId)
Data(ObjectTypeAttributeId, EventId, Data, TimeStamp, UserId)

Wow! I like it. I suppose if I wanted to then break out just Question properties, for instance, I would create a view WHERE ObjectTypeID = 3{Question Object Type}. The properties would come back as multiple records though, instead of a single record of named properties. Hmmm. Not that that would be a bad thing though... Yeah, I think that works well. Anyone else see any flaws with this? Is this a typical representation of objects in DB design?

Originally posted by sbaru This model would be generic enough so that even if the attributes for a ObjectType(Page, Section, Question etc) change, you should be fine. And all the objects irrespective of the type will go into the same table.

Which means instead of ALTER ing the table structure for new properties, you simply add them to a list. Thank-you very much. I think I will alter my model and see if I can detect any flaws on this model. Beautiful!

[blindman]

Whoa!
"If I understand you correct, my Form object table would have its own auditing table etc."
You don't understand me. That would be an administrative mess!
One table holds events for all objects, with a field indicating the name of the object.

A caveat if you plan to use system IDs from whatever database or application development platform you are working with:
In SQL Server or MS Access, and probably other platforms, these IDs bear no relation to the object itself, and thus if you run a script to generate a copy of the database or have two identical databases (for production and testing for example) the IDs for identical objects in each database will be different and you will have difficulty merging event information should that be necessary. You may even have problems when restoring a database. This is one instance where the natural name of the object makes a better index than a autonumber field. Make sure you use the full object name, including the owner.

I wouldn't get to complicated with event auditing. It's important, but it's not part of a normal user interface, receives mainly writes (so heavy indexing isn't advisable), and is generally only viewed by IT people who shouldn't need their hand held in querying and interpreting the data.

blindman