Should I create a separate table for every derived type?
<p>We are having a debate and I am struggling to decide which side to come down on.  Basically we have an event type lets say like like this (simplified example).</p>

<pre><code>public class Event
{
    public virtual int EventId { get; set; }
    public virtual DateTime DateTimeOccurred { get; set; }
}
</code></pre>

<p>Many different events in our system can inherit from this so we could have:</p>

<pre><code>public class UserUpdatedEvent : Event
{
    public virtual User User { get; set; }
}
</code></pre>

<p>So basically any event can inherit from the base event and then we can track what is happening in our system which we need to do as tasks occur off these events, etc.</p>

<p>My question is should we have a table in the DB for every event so we can enforce foreign keys in this example to the user using table per type with each sub type referencing the base Events table.  Or should we have a single table and a column, maybe linking to an enum saying what the event is and then another column containing the primary key to that object but we won't be able to enforce referential integrity (maybe with a trigger but not ideal and not full proof).</p>

<p>The pros for a single table are speed, no joins, simpler to build/maintain, etc.  The cons are no referential integrity, we are using EF and table per type does work very well, on the other hand we produce a lot of reports and these are direct SQL sprocs for speed and the joins will become pretty heavy.</p>

<p>How do people normally come down on a situation like this?</p>

<p>Many thanks for any insight into existing systems with similar problems and helping us settle this decision.</p>
