locked
Motorsport Database Design RRS feed

  • Question

  • There is obviously more to this than meets the eye! This then leads on to the second example of a motorsport database I set up a couple of years ago to keep track of the points gained by each driver during a particular season. At the end of the season the database can be reused for the next season by deleting all the drivers points and starting again.

    However, if I wanted to keep all the historical data of a previous season I guess the first thing would be that I would have to add a race date somewhere but how would I redesign it to keep track of the particular points scored for that season and that race (in Formula One the points system can change from season to season as well as new and cancelled race locations)?

    • Split by Kalman Toth Saturday, March 9, 2013 12:20 AM New topic
    Friday, March 8, 2013 11:57 PM

Answers

  • The main thing to do is to determine the time period you want to track data at and put that data in each of your tables. 

    One solution would be to create a data warehouse solution and ETL your data into the DW after every race (most likely). Then you would have the slice of history saved for each race, and would be able to look at history by season, race, etc.  It would also let you keep your database strictly focused on the current situation. 

    As I am talking through this, I wonder if the thing to do is to really get down and model the sport, along with effective times for the changeable items (members of teams, mostly... You could do times on teams too, since they do come and go as well. So the primary thing you want to model is a race, and everything else flows from there...:

    PointsSystem(PointSystemId PK, Name AK)
    Season (SeasonId PK, Name AK, StartDate, EndDate, PointsSystem)
    Race(RaceId PK, LocationId, Name, Date, SeasonId(Location, Name, Season) AK) --represents a race
    RaceDriver(RaceId, TeamMemberId, CarId, Position, PointsScored, (RaceId, DriverId) PK
    Team(TeamID PK, Name AK)
    Car (CarId PK, TeamId, Name, Brand, Number, (TeamId, Name) AK)
    TeamMemberRole( TeamMemberRoleId PK, Name AK)
    TeamMember(TeamMemberId PK, TeamId, EmployeeId, JobFunction, StartDate, EndDate, TeamMemberRoleId, (EmployeeId where EndDate is NULL) AK
    Employee(EmployeeId PK, Name, AK)

    I may have missed something, but I think that having the time on the teamMember, and making sure an employee is in the team member table only once for role, you can then trace them through any move, and can get to the team they were on for any race...I am not sure I like the car/team member reference both leading to team for the RaceDriver, but I am also not sure if a driver for one team could drive for another in some outside chance (been a while since I followed racing :) if you don't hate this idea I will be happy to flesh it out a bit more. 

     


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    • Proposed as answer by Kalman Toth Saturday, March 9, 2013 5:27 PM
    • Marked as answer by Kalman Toth Saturday, March 16, 2013 2:08 PM
    Saturday, March 9, 2013 3:39 AM

All replies

  • The main thing to do is to determine the time period you want to track data at and put that data in each of your tables. 

    One solution would be to create a data warehouse solution and ETL your data into the DW after every race (most likely). Then you would have the slice of history saved for each race, and would be able to look at history by season, race, etc.  It would also let you keep your database strictly focused on the current situation. 

    As I am talking through this, I wonder if the thing to do is to really get down and model the sport, along with effective times for the changeable items (members of teams, mostly... You could do times on teams too, since they do come and go as well. So the primary thing you want to model is a race, and everything else flows from there...:

    PointsSystem(PointSystemId PK, Name AK)
    Season (SeasonId PK, Name AK, StartDate, EndDate, PointsSystem)
    Race(RaceId PK, LocationId, Name, Date, SeasonId(Location, Name, Season) AK) --represents a race
    RaceDriver(RaceId, TeamMemberId, CarId, Position, PointsScored, (RaceId, DriverId) PK
    Team(TeamID PK, Name AK)
    Car (CarId PK, TeamId, Name, Brand, Number, (TeamId, Name) AK)
    TeamMemberRole( TeamMemberRoleId PK, Name AK)
    TeamMember(TeamMemberId PK, TeamId, EmployeeId, JobFunction, StartDate, EndDate, TeamMemberRoleId, (EmployeeId where EndDate is NULL) AK
    Employee(EmployeeId PK, Name, AK)

    I may have missed something, but I think that having the time on the teamMember, and making sure an employee is in the team member table only once for role, you can then trace them through any move, and can get to the team they were on for any race...I am not sure I like the car/team member reference both leading to team for the RaceDriver, but I am also not sure if a driver for one team could drive for another in some outside chance (been a while since I followed racing :) if you don't hate this idea I will be happy to flesh it out a bit more. 

     


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    • Proposed as answer by Kalman Toth Saturday, March 9, 2013 5:27 PM
    • Marked as answer by Kalman Toth Saturday, March 16, 2013 2:08 PM
    Saturday, March 9, 2013 3:39 AM
  • Thanks very much for such a comprehensive response. I would like the time to digest it and perhaps attempt to draw a database diagram of your model which will help to clarify things for me.

    I don't know too much about data warehousing and ETL, but this all comes nicely into something I have also been meaning to look at next which is SSAS and SSIS and how they integrate with SQL Server in relation to this type of design.

    Let me attempt ((!)(I may need some help with this!)) to draw a database diagram of it and I will be back soon...

    Saturday, March 9, 2013 12:13 PM
  • OK. I have attached a database diagram of what you suggested (or at least what I interpreted from your model...).

    Unfortunately in Formula One motorsport it is not always the best driver that wins as the car is so important with minute margins in timing between each driver.

    P.S. I have also attached the script below the model.

    USE [F1Alt]
    GO
    /****** Object:  Table [dbo].[Team]    Script Date: 03/09/2013 20:31:54 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[Team](
    	[TeamID] [int] IDENTITY(1,1) NOT NULL,
    	[Name] [varchar](50) NOT NULL,
     CONSTRAINT [PK_Team] PRIMARY KEY CLUSTERED 
    (
    	[TeamID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[TeamMemberRole]    Script Date: 03/09/2013 20:31:54 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[TeamMemberRole](
    	[TeamMemberRoleID] [int] IDENTITY(1,1) NOT NULL,
    	[Name] [varchar](50) NOT NULL,
     CONSTRAINT [PK_TeamMemberRole] PRIMARY KEY CLUSTERED 
    (
    	[TeamMemberRoleID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[PointsSystem]    Script Date: 03/09/2013 20:31:54 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[PointsSystem](
    	[PointsSystemID] [int] IDENTITY(1,1) NOT NULL,
    	[Name] [varchar](50) NOT NULL,
     CONSTRAINT [PK_PointsSystem] PRIMARY KEY CLUSTERED 
    (
    	[PointsSystemID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[Location]    Script Date: 03/09/2013 20:31:54 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[Location](
    	[LocationID] [int] IDENTITY(1,1) NOT NULL,
    	[LocationName] [varchar](50) NOT NULL,
     CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED 
    (
    	[LocationID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[Employee]    Script Date: 03/09/2013 20:31:54 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[Employee](
    	[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
    	[Name] [varchar](50) NOT NULL,
     CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
    (
    	[EmployeeID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[Car]    Script Date: 03/09/2013 20:31:54 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[Car](
    	[CarID] [int] IDENTITY(1,1) NOT NULL,
    	[TeamID] [int] NOT NULL,
    	[Name] [varchar](50) NOT NULL,
    	[Brand] [varchar](50) NOT NULL,
    	[Number] [varchar](50) NOT NULL,
     CONSTRAINT [PK_Car] PRIMARY KEY CLUSTERED 
    (
    	[CarID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[TeamMember]    Script Date: 03/09/2013 20:31:54 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[TeamMember](
    	[TeamMemberID] [int] IDENTITY(1,1) NOT NULL,
    	[TeamID] [int] NOT NULL,
    	[EmployeeID] [int] NOT NULL,
    	[TeamMemberRoleID] [int] NOT NULL,
    	[JobFunction] [varchar](50) NOT NULL,
    	[StartDate] [datetime] NOT NULL,
    	[EndDate] [datetime] NULL,
     CONSTRAINT [PK_TeamMember] PRIMARY KEY CLUSTERED 
    (
    	[TeamMemberID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[Season]    Script Date: 03/09/2013 20:31:54 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[Season](
    	[SeasonID] [int] IDENTITY(1,1) NOT NULL,
    	[PointsSystemID] [int] NOT NULL,
    	[Name] [varchar](50) NOT NULL,
    	[StartDate] [datetime] NOT NULL,
    	[EndDate] [datetime] NOT NULL,
     CONSTRAINT [PK_Season] PRIMARY KEY CLUSTERED 
    (
    	[SeasonID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[Race]    Script Date: 03/09/2013 20:31:54 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[Race](
    	[RaceID] [int] IDENTITY(1,1) NOT NULL,
    	[LocationID] [int] NOT NULL,
    	[SeasonID] [int] NOT NULL,
    	[Name] [varchar](50) NOT NULL,
    	[Date] [datetime] NOT NULL,
     CONSTRAINT [PK_Race] PRIMARY KEY CLUSTERED 
    (
    	[RaceID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object:  Table [dbo].[RaceDriver]    Script Date: 03/09/2013 20:31:54 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[RaceDriver](
    	[RaceID] [int] NOT NULL,
    	[TeamMemberID] [int] NOT NULL,
    	[CarID] [int] NOT NULL,
    	[Position] [int] NOT NULL,
    	[PointsScored] [int] NOT NULL
    ) ON [PRIMARY]
    GO
    /****** Object:  ForeignKey [FK_Car_Team]    Script Date: 03/09/2013 20:31:54 ******/
    ALTER TABLE [dbo].[Car]  WITH CHECK ADD  CONSTRAINT [FK_Car_Team] FOREIGN KEY([TeamID])
    REFERENCES [dbo].[Team] ([TeamID])
    GO
    ALTER TABLE [dbo].[Car] CHECK CONSTRAINT [FK_Car_Team]
    GO
    /****** Object:  ForeignKey [FK_Race_Location]    Script Date: 03/09/2013 20:31:54 ******/
    ALTER TABLE [dbo].[Race]  WITH CHECK ADD  CONSTRAINT [FK_Race_Location] FOREIGN KEY([LocationID])
    REFERENCES [dbo].[Location] ([LocationID])
    GO
    ALTER TABLE [dbo].[Race] CHECK CONSTRAINT [FK_Race_Location]
    GO
    /****** Object:  ForeignKey [FK_Race_Season]    Script Date: 03/09/2013 20:31:54 ******/
    ALTER TABLE [dbo].[Race]  WITH CHECK ADD  CONSTRAINT [FK_Race_Season] FOREIGN KEY([SeasonID])
    REFERENCES [dbo].[Season] ([SeasonID])
    GO
    ALTER TABLE [dbo].[Race] CHECK CONSTRAINT [FK_Race_Season]
    GO
    /****** Object:  ForeignKey [FK_RaceDriver_Car]    Script Date: 03/09/2013 20:31:54 ******/
    ALTER TABLE [dbo].[RaceDriver]  WITH CHECK ADD  CONSTRAINT [FK_RaceDriver_Car] FOREIGN KEY([CarID])
    REFERENCES [dbo].[Car] ([CarID])
    GO
    ALTER TABLE [dbo].[RaceDriver] CHECK CONSTRAINT [FK_RaceDriver_Car]
    GO
    /****** Object:  ForeignKey [FK_RaceDriver_Race]    Script Date: 03/09/2013 20:31:54 ******/
    ALTER TABLE [dbo].[RaceDriver]  WITH CHECK ADD  CONSTRAINT [FK_RaceDriver_Race] FOREIGN KEY([RaceID])
    REFERENCES [dbo].[Race] ([RaceID])
    GO
    ALTER TABLE [dbo].[RaceDriver] CHECK CONSTRAINT [FK_RaceDriver_Race]
    GO
    /****** Object:  ForeignKey [FK_RaceDriver_TeamMember]    Script Date: 03/09/2013 20:31:54 ******/
    ALTER TABLE [dbo].[RaceDriver]  WITH CHECK ADD  CONSTRAINT [FK_RaceDriver_TeamMember] FOREIGN KEY([TeamMemberID])
    REFERENCES [dbo].[TeamMember] ([TeamMemberID])
    GO
    ALTER TABLE [dbo].[RaceDriver] CHECK CONSTRAINT [FK_RaceDriver_TeamMember]
    GO
    /****** Object:  ForeignKey [FK_Season_PointsSystem]    Script Date: 03/09/2013 20:31:54 ******/
    ALTER TABLE [dbo].[Season]  WITH CHECK ADD  CONSTRAINT [FK_Season_PointsSystem] FOREIGN KEY([PointsSystemID])
    REFERENCES [dbo].[PointsSystem] ([PointsSystemID])
    GO
    ALTER TABLE [dbo].[Season] CHECK CONSTRAINT [FK_Season_PointsSystem]
    GO
    /****** Object:  ForeignKey [FK_TeamMember_Employee]    Script Date: 03/09/2013 20:31:54 ******/
    ALTER TABLE [dbo].[TeamMember]  WITH CHECK ADD  CONSTRAINT [FK_TeamMember_Employee] FOREIGN KEY([EmployeeID])
    REFERENCES [dbo].[Employee] ([EmployeeID])
    GO
    ALTER TABLE [dbo].[TeamMember] CHECK CONSTRAINT [FK_TeamMember_Employee]
    GO
    /****** Object:  ForeignKey [FK_TeamMember_Team]    Script Date: 03/09/2013 20:31:54 ******/
    ALTER TABLE [dbo].[TeamMember]  WITH CHECK ADD  CONSTRAINT [FK_TeamMember_Team] FOREIGN KEY([TeamID])
    REFERENCES [dbo].[Team] ([TeamID])
    GO
    ALTER TABLE [dbo].[TeamMember] CHECK CONSTRAINT [FK_TeamMember_Team]
    GO
    /****** Object:  ForeignKey [FK_TeamMember_TeamMemberRole]    Script Date: 03/09/2013 20:31:54 ******/
    ALTER TABLE [dbo].[TeamMember]  WITH CHECK ADD  CONSTRAINT [FK_TeamMember_TeamMemberRole] FOREIGN KEY([TeamMemberRoleID])
    REFERENCES [dbo].[TeamMemberRole] ([TeamMemberRoleID])
    GO
    ALTER TABLE [dbo].[TeamMember] CHECK CONSTRAINT [FK_TeamMember_TeamMemberRole]
    GO



    • Edited by meridius10 Saturday, March 9, 2013 6:37 PM
    Saturday, March 9, 2013 6:25 PM