The following is the schema I developed for the different tables. I am not using any type of naming convention for the table names, but I do with Stored Procedures and views. The naming convention for the Stored Procedures is to put a p_ then a description of the function and finally the table name, or a description or the joins being used. For example, a query selecting all records in the Players table would be called p_get_players.
CREATE TABLE [dbo].[Players] (
[Player_id] [int] IDENTITY (1, 1) NOT NULL ,
[Team_ID] [int] NOT NULL ,
[Position_ID] [int] NOT NULL ,
[PlayerName] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InjuryStatus] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Positions] (
[PositionID] [int] IDENTITY (1, 1) NOT NULL ,
[Position] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Stats] (
[Stats_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Player_ID] [int] NOT NULL ,
[PassingYds] [int] NULL ,
[PassingAttmpts] [int] NULL ,
[PassingComplt] [int] NULL ,
[PassingTDs] [int] NULL ,
[Inteceptions] [int] NULL ,
[PassingLong] [int] NULL ,
[RuningYds] [int] NULL ,
[RunningAttmpts] [int] NULL ,
[RunningAvg] [int] NULL ,
[RunningTD] [int] NULL ,
[RunningLong] [int] NULL ,
[GameNumber] [int] NOT NULL ,
[Year] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Teams] (
[TeamID] [int] IDENTITY (1, 1) NOT NULL ,
[TeamName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TeamCity] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TeamState] [nchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Currently it's very simple, with Team, Player, Positions and Stats as the main table. The first report in the application uses Stats, Player and Team. In the second installment of this article, I'll step you through the code and pages.