Sql-server – SQL Data type for “Reference” fields in table – SQL Server

sql serversql-server-2008

I am learning SQL Server. I need a bit of advice regarding data type for fields that will be referred to but will not actually themselves require values. I don't know the correct term to use(edit welcome).

I will draw a brief overview of the fields functions to try and illustrate requirement clearer. The fields with the asterisks I am calling "reference" fields, there are many more event types but just keeping example brief and hopefully clear. Table-Events should be like a junction table. I am trying to change my ERD (of which this is a small section) so that my events aren't hardcoded and I reduce redundancy and if in future new EventTypes are needed I can just update EventTypes to add them.

Edit: Each Column here represents a table with the first Table- representing the table name the second field the GUID field etc.

Table-Player     Table-Events      Table-EventTypes
PlayerID         EventsID          EventTypeID 
firstName        Minute            Try*
secondName       Fk_EventType      Conversion* 
Fk_team          Fk_Player         Penalty*

As such though Try/Conversion/Penalty will never have any data it will just be selected in the Events Table with Fk_Player and minute. So what data type should I use.

Best Answer

Here is what I would do based on your requirements:

CREATE DATABASE [demo];
GO

USE [demo];



CREATE TABLE [dbo].[Player](
    [playerID] [int] IDENTITY(1,1) NOT NULL,
    [firstName] [varchar](50) NULL,
    [secondName] [varchar](50) NULL,
    [fk_team] [int] NULL,
 CONSTRAINT [PK_Player] PRIMARY KEY CLUSTERED ([playerID] ASC)
 );
GO


CREATE TABLE [dbo].[EventTypes](
    [EventTypeID] [int] IDENTITY(1,1) NOT NULL,
    [EventType] [varchar](50) NULL,
 CONSTRAINT [PK_EventTypes] PRIMARY KEY CLUSTERED ([EventTypeID] ASC)
 );

GO


CREATE TABLE [dbo].[Events](
    [eventsID] [int] IDENTITY(1,1) NOT NULL,
    [Minute] [varchar](50) NULL,
    [fk_EventType] [int] NULL,
    [fk_Player] [int] NULL,
 CONSTRAINT [PK_Events] PRIMARY KEY CLUSTERED ( [eventsID] ASC)
 );
GO
SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Events]  WITH CHECK ADD  
CONSTRAINT [FK_Events_EventTypes] FOREIGN KEY([fk_EventType])
REFERENCES [dbo].[EventTypes] ([EventTypeID])
GO

ALTER TABLE [dbo].[Events] CHECK CONSTRAINT [FK_Events_EventTypes]
GO

ALTER TABLE [dbo].[Events]  WITH CHECK ADD  
CONSTRAINT [FK_Events_Player] FOREIGN KEY([fk_Player])
REFERENCES [dbo].[Player] ([playerID])
GO

ALTER TABLE [dbo].[Events] CHECK CONSTRAINT [FK_Events_Player]
GO



INSERT INTO [dbo].[EventTypes] ([EventType]) 
      VALUES('Try'), ('Conversion'), ('Penalty');
GO
select * from EventTypes;

This should be what you need. Have a great day!

Related Question