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 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.

share|improve this question

migrated from stackoverflow.com Apr 11 '12 at 14:50

This question came from our site for professional and enthusiast programmers.

    
fields that will be referred to but will not actually themselves require values - is actually NULL fields. –  Gustavo Freddo Apr 11 '12 at 1:26
    
I think you are getting a little confused between columns names and the contents of the columns. "Try", "Conversion" and "Penalty" should be the contents of a column "EventType", at least if I was designing it :-) –  James Anderson Apr 11 '12 at 2:01
    
@JamesAnderson Yes Try Conversion and Penalty are part of my Event Type column. But what values do i assign them when they are referenced by the Table-Events table but don't actually require values themselves. –  sayth Apr 11 '12 at 4:11

1 Answer 1

up vote 2 down vote accepted

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!

share|improve this answer
    
when you are inserting Try converion and penalty into EventTypes what types are they being set as? –  sayth Apr 11 '12 at 4:16
    
They show up as varchar's mapped to integers using the FK. –  Steve Stedman Apr 11 '12 at 4:25
    
EventTypeID EventType ----------- -------------------------------------------------- 1 Try 2 Conversion 3 Penalty (3 row(s) affected) –  Steve Stedman Apr 11 '12 at 4:25
    
If I am using the database designer not direct SQL is there a way to acheive this or should i just assign them as varchar? –  sayth Apr 11 '12 at 4:26
    
@sayth I would recommend 2 things: 1) Open up SSMS, connect to a server you can test stuff on (A local server hopefully) and connect to the master datbase. Then you can run this script. 2) Learn to do things through SQL. The most powerful way to interface with your database is to use SQL, and most of the UI elements just run SQL that sets things or alters things for you. Those are great to use for productivity, but if you need to something that is possible but the UI doesn't have something for, you'll need to use SQL. And it's good to learn anyway when working with databases. –  Jeremy Pridemore Apr 11 '12 at 5:49

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.