I have an Events table whose goal is to store actions done by web site users. An action basically alters or create a new row in a table X. This will allow me to store an history of all actions done by a user. As such, Events contains:

* a primary key column
* a text to describe the event (ex: "posted a comment")
* a discrimator column if needed
* a foreign key column to another table A
* a foreign key column to another table B
* ....
* a foreign key column to another table N

A row in the Events table will have only one of the foreign key columns set, all others will be null (so they are all nullable). The table behaves like an indirection table to the actual table concerned by the event. I don't know if a discriminator is needed since all the information is contained in the foreign key columns. Tables A to N can be anything. Their domain model class can have a common interface if necessary (IEventRecordable).

My question is: Is a mapping possible between the Events table and an Event class? Is it especially feasible with fluent nhibernate? Can it be done without having to create many derived classes of Event (I don't want to create so many empty subclasses)? The Event class would ideally be as follows:

public class Event
{
public virtual int Id { get; set; }
public virtual IEventRecordable ActualEvent { get; set; }
public virtual string EventDescription { get; set; }
DateTime EventDateTime { get; set; }
}

Many classes among the domain model classes could implement IEventRecordable (which is mainly an empty interface). It could be the User table or a BlogComment table...

Thanks

I would create a view that returned the type (or table name) that is linked from your Events table. Map the view to the Event class and create a method to return the linked entity now that you know its type and id.

Thanks for your answer. However I'm new to all those things and your explanation is like chinese to me. Can you elaborate, give some details? Thank you.

Create a view that uses a case statement to return the table name and id for the foreign key column in use:



Map that to your Event class. Create a method on your event class to load the IEventRecordable:



It's ugly but any solution will be ugly due to the structure of your events table. If it's a new application, I would structure your Events table to store TableName, ForeignKey instead of N columns. I wouldn't worry about enforcing foreign keys on an audit table.

Hope this helps.

Thank you, I understand what you mean now.
But what about the session in the "Event" domain model class? I have no session there. No way to do otherwise?

I thought you might ask about that. I would map the TableName in your Event class and create a read-only property that returned Type using a switch statement. Then your UI code or a repository could load the referenced object using the type and id from your Event class.

There might be a better way: Use the view to turn your FK columns into a discriminator. You can map an interface in NHibernate and use the discriminator to return the object. I haven't tried this but it might work.

hi sorry if this thread is too old, but i am interested on how to the mapping like quoted above. how do i define mapping for events table that is related to many tables (hence many entities), and is discriminated by the column "TableName".