Creating a reference table.
-
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
-
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 -
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
-
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 -
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
-
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 -
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.
-
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.
-
I was thinking of a child table for each of the three columns. Otherwise I'm not sure I understand your question.
-
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply