Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Creating a reference table. Expand / Collapse
Author
Message
Posted Thursday, March 7, 2013 12:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, June 28, 2014 12:40 AM
Points: 14, Visits: 22
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
Post #1428185
Posted Thursday, March 7, 2013 1:49 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:02 PM
Points: 7,081, Visits: 12,574
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


  Post Attachments 
513871a3_2.jpg (71 views, 57.93 KB)
Post #1428250
Posted Thursday, March 7, 2013 1:58 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, June 28, 2014 12:40 AM
Points: 14, Visits: 22
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
Post #1428257
Posted Thursday, March 7, 2013 2:02 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:02 PM
Points: 7,081, Visits: 12,574
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
Post #1428259
Posted Thursday, March 7, 2013 2:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, June 28, 2014 12:40 AM
Points: 14, Visits: 22
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
Post #1428263
Posted Thursday, March 7, 2013 2:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:02 PM
Points: 7,081, Visits: 12,574
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
Post #1428278
Posted Thursday, March 7, 2013 3:41 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 4:07 PM
Points: 646, Visits: 3,785
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.
Post #1428306
Posted Thursday, March 7, 2013 5:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, June 28, 2014 12:40 AM
Points: 14, Visits: 22
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.
Post #1428331
Posted Thursday, March 7, 2013 5:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, June 28, 2014 12:40 AM
Points: 14, Visits: 22
I was thinking of a child table for each of the three columns. Otherwise I'm not sure I understand your question.
Post #1428333
Posted Friday, March 8, 2013 7:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:02 PM
Points: 7,081, Visits: 12,574
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
Post #1428587
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse