Modeling a Generic Relationship (expressed in C#) in a Database

This is most likely one for all you sexy DBAs out there:

How would I effieciently model a relational database whereby I have a field in an "Event" table which defines a "SportType"?

This "SportsType" field can hold a l开发者_JAVA技巧ink to different sports tables E.g. "FootballEvent", "RubgyEvent", "CricketEvent" and "F1 Event".

Each of these Sports tables have different fields specific to that sport.

My goal is to be able to genericly add sports types in the future as required, yet hold sport specific event data (fields) as part of my Event Entity.

Is it possible to use an ORM such as NHibernate / Entity framework / DataObjects.NET which would reflect such a relationship?

I have thrown together a quick C# example to express my intent at a higher level:

public class Event<T> where T : new()
{
    public T Fields { get; set; }

    public Event()
    {
        EventType = new T();
    }
}

public class FootballEvent
{
    public Team CompetitorA { get; set; }  
    public Team CompetitorB { get; set; }    
}

public class TennisEvent
{
    public Player CompetitorA { get; set; }
    public Player CompetitorB { get; set; }
}

public class F1RacingEvent
{
    public List<Player> Drivers { get; set; }
    public List<Team> Teams { get; set; }
}

public class Team
{
    public IEnumerable<Player> Squad { get; set; }
}

public class Player
{
    public string Name { get; set; }
    public DateTime DOB { get; set;}
}

DataObjects.Net supports automatic mappings for open generics. Some details on this are described here.

You can do this by having all the Event types inherit from an abstract Event base class. This make sense to me because all the events share some common properties: date, venue, etc. You can use a table per concrete class or table per subclass strategy to store the objects in a relational database. Here are some links to articles describing inheritance mapping with NHibernate:

• Chapter 8. Inheritance Mapping
• Fluent NHibernate and Inheritance Mapping
• NHibernate Mapping – Inheritance

The example converted to DO4 must look as follows:

// I'd add this type - adding an abstract base makes design more clean + allows you to share
// the behavior among all the descendants
[Serializable]
[HierarchyRoot]
public abstract class EventBase : Entity
{
  [Key]
  Guid Id { get; private set; } // Or some other type
}

[Serializable]
public class Event<T> : EventBase
    where T : IEntity, new() // IEntity indicates DO4 must try  to map its descendants automatically
    // Although I'd put some stronger requirement, e.g. by using IEventData instead of IEntity here
{
    public T Data { get; set; }

    public Event(T data)
    {
        Data = data;
    }
}

[Serializable]
[HierarchyRoot]
public class FootballEvent
{
    // You need [Key]  here
    public Team CompetitorA { get; set; } 
    public Team CompetitorB { get; set; }   
}

[Serializable]
[HierarchyRoot]
public class TennisEvent
{
    // You need [Key]  here
    public Player CompetitorA { get; set; }
    public Player CompetitorB { get; set; }
}

[Serializable]
[HierarchyRoot]
public class F1RacingEvent
{
    // You need [Key]  here
    public EntitySet<Player> Drivers { get; private set; }
    public EntitySet<Team> Teams { get; private set; }
}

[Serializable]
[HierarchyRoot]
public class Team
{
    // You need [Key]  here
    public EntitySet<Player> Squad { get; set; }
}

[Serializable]
[HierarchyRoot]
public class Player
{
    public string Name { get; set; }
    public DateTime DOB { get; set; }
}

In this case Event instances will be available (= mapped automatically) for all suitable Ts from model. E.g. in this case they'll be: - EventBase // Yes, even it, coz it's suitable - FootballEvent - TennisEvent - F1RacingEvent - Team - Player

If you'd like to restrict this to just certain types, you must do the following: - Add an interface inherited from IEntity all these types will support, e.g. IEventData. - Use it as generic parameter constraint for generic parameter T in Event.

Cross Posted from: http://forum.x-tensive.com/viewtopic.php?f=29&t=5820, Answer By Alex Yakunin, Chief Executive DataObjects.NET

There are a bunch of options like XML columns and EAV (also known as database within a database), but none of which will translate well with an ORM to traditional static object-oriented languages, and all of which have drawbacks with respect to data type safety and referential integrity at the database level.

If you need this level of dynamic structure in both the database and the client, you might need to go with an object or document database (and language) which is much more dynamic by design - relational databases tend to work best with static relationships and data models.