Modeling a Generic Relationship in a Database
- by StevenH
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 link 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 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;}
}
