InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 19:51:50 GMT20http://www.sqlservercentral.com/Forums/Topic1428185-1292-1.aspx[quote][b]brian.geregach (3/7/2013)[/b][hr]I was thinking of a child table for each of the three columns. Otherwise I'm not sure I understand your question.[/quote]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?[code="sql"]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);GOCREATE UNIQUE INDEX [IX_dbo.TicketPriority.Name] ON dbo.TicketPriority (Name); -- alternate keyGOCREATE 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));[/code]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.Fri, 08 Mar 2013 07:58:15 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1428185-1292-1.aspxI was thinking of a child table for each of the three columns. Otherwise I'm not sure I understand your question.Thu, 07 Mar 2013 17:42:35 GMTbrian.geregachhttp://www.sqlservercentral.com/Forums/Topic1428185-1292-1.aspxThank 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.Thu, 07 Mar 2013 17:38:37 GMTbrian.geregachhttp://www.sqlservercentral.com/Forums/Topic1428185-1292-1.aspxBrian,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.Thu, 07 Mar 2013 15:41:50 GMTChrissy321http://www.sqlservercentral.com/Forums/Topic1428185-1292-1.aspx[quote][b]brian.geregach (3/7/2013)[/b][hr]The columns that I think should go into their own table are:Priority - a number from 1 - 4Assignee - User or group ticket is assigned toCategory - 1st level category assignmentPriority will probably never change as we only use the 4 levels.Assignee will only change when we add new people to work on these ticketsCategory is really our vendors name and will only change when we get new vendors.Brian[/quote]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.Thu, 07 Mar 2013 14:55:27 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1428185-1292-1.aspxThe columns that I think should go into their own table are:Priority - a number from 1 - 4Assignee - User or group ticket is assigned toCategory - 1st level category assignmentPriority will probably never change as we only use the 4 levels.Assignee will only change when we add new people to work on these ticketsCategory is really our vendors name and will only change when we get new vendors.BrianThu, 07 Mar 2013 14:10:50 GMTbrian.geregachhttp://www.sqlservercentral.com/Forums/Topic1428185-1292-1.aspx[quote][b]brian.geregach (3/7/2013)[/b][hr]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[/quote]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?Thu, 07 Mar 2013 14:02:02 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1428185-1292-1.aspxhere 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.BrianThu, 07 Mar 2013 13:58:31 GMTbrian.geregachhttp://www.sqlservercentral.com/Forums/Topic1428185-1292-1.aspxFirst off, welcome to SQL Server Central (SSC). This is a great community-site to learn more about SQL Server.[quote][b]brian.geregach (3/7/2013)[/b][hr]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.[/quote]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.[img]http://www.sqlservercentral.com/Forums/Attachment13308.aspx[/img][quote]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?[/quote]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:[code="sql"]SELECT pt.KeyCOlumn, pt.SomeColumn, ct.OtherColumnFROM ParentTable pt JOIN ChildTable ct ON pt.KeyColumn = ct.KeyColumn;[/code]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: [u][url=http://www.amazon.com/Microsoft%C2%AE-Server%C2%AE-T-SQL-Fundamentals-PRO-Developer/dp/0735626014/ref=la_B001IGQENW_1_6?ie=UTF8&qid=1362689278&sr=1-6]Microsoft® SQL Server® 2008 T-SQL Fundamentals[/url][/u][u][url=http://www.amazon.com/Microsoft-Server-2012-T-SQL-Fundamentals/dp/0735658145/ref=la_B001IGQENW_1_2?ie=UTF8&qid=1362689278&sr=1-2]Microsoft® SQL Server® 2012 T-SQL Fundamentals[/url][/u]Thu, 07 Mar 2013 13:49:47 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1428185-1292-1.aspxI 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 youBrianThu, 07 Mar 2013 12:08:32 GMTbrian.geregach