2

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.

2

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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