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 / 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;}
}
