Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am not sure if I am thinking about this process the wrong way.

Just to Surmise if I create a table(TeamList) whose sole purpose is to capture the coaches team list of players and positions for a particular round and date.

I have a Fk from PlayersTeams - A junction table that defines which players play for what teams.

A round table Fk - defines what round the list is for

In the TeamList table I have the two aforementioned columns, plus a date (mm/dd/yyyy) column, a TeamName column, and 17 player position columns.

To ensure that each player in the team list is only from the same team do I need 17 foreign key constraints from PlayersTeams(Fk) for each position to ensure that only a player from the selected team can be selected in a given position?

have I got something minged up?

Edit as below.

USE [NRL_DataMartDesign]
GO

/****** Object:  Table [dbo].[Junct_PlayersTeams]    Script Date: 05/02/2012 14:11:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Junct_PlayersTeams](
    [TeamPlayer] [int] IDENTITY(1,1) NOT NULL,
    [fk_Players] [int] NULL,
    [fk_Teams] [int] NULL,
    [startDate] [datetime] NULL,
    [endDate] [datetime] NULL,
 CONSTRAINT [PK_Junct_PlayersTeams] PRIMARY KEY CLUSTERED 
(
    [TeamPlayer] 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

ALTER TABLE [dbo].[Junct_PlayersTeams]  WITH CHECK ADD  CONSTRAINT [FK_Junct_PlayersTeams_Players] FOREIGN KEY([fk_Players])
REFERENCES [dbo].[Players] ([PLayerID])
GO

ALTER TABLE [dbo].[Junct_PlayersTeams] CHECK CONSTRAINT [FK_Junct_PlayersTeams_Players]
GO

ALTER TABLE [dbo].[Junct_PlayersTeams]  WITH CHECK ADD  CONSTRAINT [FK_Junct_PlayersTeams_Team] FOREIGN KEY([fk_Teams])
REFERENCES [dbo].[Team] ([TeamID])
GO

ALTER TABLE [dbo].[Junct_PlayersTeams] CHECK CONSTRAINT [FK_Junct_PlayersTeams_Team]
GO

Team List Table

USE [NRL_DataMartDesign]
GO

/****** Object:  Table [dbo].[TeamList]    Script Date: 05/02/2012 14:13:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TeamList](
    [TeamListID] [int] IDENTITY(1,1) NOT NULL,
    [Round(Fk)] [int] NULL,
    [Team(Fk)] [int] NULL,
    [Coach(Fk)] [int] NULL,
    [Fullback] [int] NULL,
    [LeftWing] [int] NULL,
    [LeftCentre] [int] NULL,
    [RightCentre] [int] NULL,
    [RightWing] [int] NULL,
    [FiveEighth] [int] NULL,
    [HalfBack] [int] NULL,
    [Lock] [int] NULL,
    [SecondRow(L)] [int] NULL,
    [SecondRow(R)] [int] NULL,
    [Prop(L)] [int] NULL,
    [Hooker] [int] NULL,
    [Prop(R)] [int] NULL,
    [Interchange1] [int] NULL,
    [Interchange2] [int] NULL,
    [Interchange3] [int] NULL,
    [Interchange4] [int] NULL,
 CONSTRAINT [PK_TeamList] PRIMARY KEY CLUSTERED 
(
    [TeamListID] 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

ALTER TABLE [dbo].[TeamList]  WITH CHECK ADD  CONSTRAINT [FK_TeamList_Junc_TeamCoach] FOREIGN KEY([Coach(Fk)])
REFERENCES [dbo].[Junc_TeamCoach] ([Junct_TeamCoach])
GO

ALTER TABLE [dbo].[TeamList] CHECK CONSTRAINT [FK_TeamList_Junc_TeamCoach]
GO

ALTER TABLE [dbo].[TeamList]  WITH CHECK ADD  CONSTRAINT [FK_TeamList_Junct_PlayersTeams] FOREIGN KEY([Team(Fk)])
REFERENCES [dbo].[Junct_PlayersTeams] ([TeamPlayer])
GO

ALTER TABLE [dbo].[TeamList] CHECK CONSTRAINT [FK_TeamList_Junct_PlayersTeams]
GO
share|improve this question
    
Can you please describe your tables using CREATE TABLE syntax (which you can generate from Management Studio) or some kind of diagram? It's late here and the word problem is giving me vertigo. –  Aaron Bertrand May 2 '12 at 3:55
    
@AaronBertrand updated with create table syntax –  sayth May 2 '12 at 4:14
    
Just a thought and not a full answer: you may want to move that validation logic to the operation (i.e. stored proc) that is inserting the data instead of trying to enforce it via the schema. –  WT_W May 2 '12 at 4:35
    
So to summarize what you're trying to do: you want to store a team makeup in a single row, with a column for all 17 positions, and then enforce that they all came from the same team with 17 foreign key constraints? Are you open to better overall design suggestions (including a different schema approach altogether), or are you set on this schema and you just want suggestions for this specific data model enforcement? –  Aaron Bertrand May 2 '12 at 4:37
    
@AaronBertrand open to design improvements. I am just learning really and always open to suggestions. –  sayth May 2 '12 at 4:44

1 Answer 1

up vote 3 down vote accepted

Instead of one column per position in TeamList, you can use lookup tables for Positions and Players. The TeamList table then defines which player is in which team and in what position for each round. You then don't need to enforce integrity on all players being from the same team because there is one row per player that defines the player:team relationship in TeamList (playerID and teamID).

I've included some DDL below as an example - you may need to add more data columns,etc. but it should be a good starting point.

Positions table:

 CREATE TABLE [dbo].[Positions]
    (
        [PositionID] [int] NOT NULL,
        [position_name] [varchar](20) NULL,
    PRIMARY KEY CLUSTERED 
    (
        [PositionID] 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

The players table can be created as (not including team associations, but you could add this here):

CREATE TABLE [dbo].[Players](
    [playerID] [int] IDENTITY(1,1) NOT NULL,
    [player_name] [varchar](500) NULL,
PRIMARY KEY CLUSTERED 
(
    [playerID] 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

You can then create your TeamList table like so

 CREATE TABLE [dbo].[TeamList]
    (
    [TeamListID] [int] IDENTITY(1,1) NOT NULL,
    [Round(Fk)] [int] NULL,
    [Team(Fk)] [int] NULL,
    [Coach(Fk)] [int] NULL,
    [PositionID] int null,
    [PlayerID] int not null,
    CONSTRAINT [PK_TeamList] PRIMARY KEY CLUSTERED 
    (
    [TeamListID] 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

    ALTER TABLE [dbo].[TeamList]  WITH CHECK ADD  CONSTRAINT [FK_TeamList_Positions] FOREIGN KEY([Position])
    REFERENCES [dbo].[Positions] ([PositionID])
    GO

    ALTER TABLE [dbo].[TeamList] CHECK CONSTRAINT [FK_TeamList_Positions]
    GO

ALTER TABLE [dbo].[TeamList]  WITH CHECK ADD  CONSTRAINT [FK_TeamList_Players] FOREIGN KEY([PlayerID])
REFERENCES [dbo].[Players] ([playerID])
GO

ALTER TABLE [dbo].[TeamList] CHECK CONSTRAINT [FK_TeamList_Players]
GO

-- Original FK definitions on round, team, coach, etc. go here
share|improve this answer
    
does this mean though that any text could be entered into the position field? Is there a way to lock off the positions table after I have entered my positions so it can't be edited? –  sayth May 2 '12 at 8:31
    
In theory yes another position could be added to this table. But unless you provide a method to do so in the application end users will not be able to. Could still be edited direct in the database, but what can't. –  Ally Reilly May 2 '12 at 9:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.