Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Creating a reference table.


Creating a reference table.

Author
Message
brian.geregach
brian.geregach
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 28
I am very new to SQL Server. I have a single table database, by this I mean I have created only one table. I have columns that I think would do well holding the data in it's own table and then create a PK to FK relationship. Also, is there anything special I have to do to query the primary table to give me the data that is in the new table?

Thank you

Brian
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8233 Visits: 14368
First off, welcome to SQL Server Central (SSC). This is a great community-site to learn more about SQL Server.

brian.geregach (3/7/2013)
I am very new to SQL Server. I have a single table database, by this I mean I have created only one table. I have columns that I think would do well holding the data in it's own table and then create a PK to FK relationship.

What has you thinking that? This simplest case where this type of normalization typically makes sense is when you can reduce the number of rows in what becomes the primary (parent) table, i.e. when you have a one-to-many relationship between the parent table and child table. Would you mind posting the DDL for your table, i.e. the CREATE TABLE statement? You can retrieve it by right-clicking the table within SQL Server Management Studio (SSMS) and using the "Script table as" menu.



Also, is there anything special I have to do to query the primary table to give me the data that is in the new table?

Once you have split the one table into two and related them via a PK / FK relationship, then to bring them together again you will use a query that contains a JOIN clause, like this:

SELECT  pt.KeyCOlumn,
pt.SomeColumn,
ct.OtherColumn
FROM ParentTable pt
JOIN ChildTable ct ON pt.KeyColumn = ct.KeyColumn;



If you're just starting out I would recommend any book from Itzik Ben-Gan. Here are two good ones, one each for SQL 2008 and SQL 2012:

Microsoft® SQL Server® 2008 T-SQL Fundamentals


Microsoft® SQL Server® 2012 T-SQL Fundamentals


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Attachments
513871a3_2.jpg (71 views, 57.00 KB)
brian.geregach
brian.geregach
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 28
here is the information you were asking for:

CREATE TABLE [dbo].[Tickets](
   [Ticket Number] [int] NOT NULL,
   [Priority] [smallint] NULL,
   [Assignee] [varchar](25) NULL,
   [Category] [varchar](25) NULL,
   [Vendor Ticket] [varchar](60) NULL,
   [Open Date] [date] NULL,
   [Opener] [varchar](30) NULL,
   [Closer] [varchar](30) NULL
) ON [PRIMARY]

Also, I already own the Microsoft SQL Server 2012 T-SQL Fundamentals book by Ltzik Ben-Gan.

Thank you for the advise.

Brian
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8233 Visits: 14368
brian.geregach (3/7/2013)
here is the information you were asking for:

CREATE TABLE [dbo].[Tickets](
   [Ticket Number] [int] NOT NULL,
   [Priority] [smallint] NULL,
   [Assignee] [varchar](25) NULL,
   [Category] [varchar](25) NULL,
   [Vendor Ticket] [varchar](60) NULL,
   [Open Date] [date] NULL,
   [Opener] [varchar](30) NULL,
   [Closer] [varchar](30) NULL
) ON [PRIMARY]

Also, I already own the Microsoft SQL Server 2012 T-SQL Fundamentals book by Ltzik Ben-Gan.

Thank you for the advise.

Brian

Cool. Which columns were you thinking of moving into a child table? What have you considered as a possible key column to relate the two tables?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
brian.geregach
brian.geregach
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 28
The columns that I think should go into their own table are:
Priority - a number from 1 - 4
Assignee - User or group ticket is assigned to
Category - 1st level category assignment

Priority will probably never change as we only use the 4 levels.
Assignee will only change when we add new people to work on these tickets
Category is really our vendors name and will only change when we get new vendors.

Brian
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8233 Visits: 14368
brian.geregach (3/7/2013)
The columns that I think should go into their own table are:
Priority - a number from 1 - 4
Assignee - User or group ticket is assigned to
Category - 1st level category assignment

Priority will probably never change as we only use the 4 levels.
Assignee will only change when we add new people to work on these tickets
Category is really our vendors name and will only change when we get new vendors.

Brian

It sounds like there will be a one-to-one relationship between these columns and the ones that would be left in what would become the parent table, i.e. both tables will have the same number of rows, in which case I would recommend leaving it as a single table. With the few number of columns you have and no implied normalization of the data by moving columns to a child table, I see no reason to complicate your data model by introducing a child table.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Chrissy321
Chrissy321
Say Hey Kid
Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)Say Hey Kid (668 reputation)

Group: General Forum Members
Points: 668 Visits: 4571
Brian,

You may want to consider removing the spaces from you field names. TicketNumber rather than Ticket Number. Spaces are allowed but you would have to bracket you fields when referring to them.

Also consider whether you can change any of your fields from NULL to NOT NULL. Your requirements will determine whether this is possible. It may improve the robustness of your data model to say, whenever a ticket is created it must be given a priority. Again your requirements will determine this but you could improve the validity of your data by requiring fields when possible.

Also consider a key field to prevent duplicate data. TicketNumber would seem to be a natural for this.
brian.geregach
brian.geregach
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 28
Thank you for your insight. My primary key is the ticket number. I will definitely look at changing some of the columns to or to not require nulls.
brian.geregach
brian.geregach
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 28
I was thinking of a child table for each of the three columns. Otherwise I'm not sure I understand your question.
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8233 Visits: 14368
brian.geregach (3/7/2013)
I was thinking of a child table for each of the three columns. Otherwise I'm not sure I understand your question.

OK, I think I know what you mean now. Looking at the columns I think you were thinking lookup tables, not normalizing columns out of the Ticket table based on the TicketNumber.

Something like this for moving Priority out of the Ticket table?

CREATE TABLE dbo.TicketPriority (
TicketPriorityID TINYINT NOT NULL
CONSTRAINT [PK_dbo.TicketPriority] PRIMARY KEY CLUSTERED,
-- renamed TicketPriority because Priority is a keyword
Name VARCHAR(100) NOT NULL);
GO

CREATE UNIQUE INDEX [IX_dbo.TicketPriority.Name] ON dbo.TicketPriority (Name); -- alternate key
GO

CREATE TABLE dbo.Ticket
(
TicketNumber INT NOT NULL CONSTRAINT [PK_dbo.Ticket] PRIMARY KEY CLUSTERED,
TicketPriorityID TINYINT NOT NULL, -- refactored into lookup table
Assignee VARCHAR(25) NULL,
Category VARCHAR(25) NULL,
VendorTicket VARCHAR(60) NULL,
OpenDate DATE NULL,
Opener VARCHAR(30) NULL,
Closer VARCHAR(30) NULL,
CONSTRAINT [FK_dbo.Ticket_dbo.TicketPriority.TicketPriorityID]
FOREIGN KEY (TicketPriorityID)
REFERENCES dbo.TicketPriority (TicketPriorityID));



Note: I agree with Chrissy on the naming and some of those changes are reflected in my sample code. I hadn't even dove into the table column names themselves until just now, I was still thinking about the design question of how to split the table. I also went singular on the table name and changed Priority to TicketPriority since Priority is a keyword. I tend to have better luck using singular table names especially when it comes time to create linking-tables, e.g. in my opinion a table named TicketVenue sounds better than TicketsVenues.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search