February 21, 2019 at 7:58 am
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[HSN](
[MSN] [float] NULL,
[SN] [nvarchar](255) NULL,
[RN] [nvarchar](255) NULL,
[DQ] [nvarchar](255) NULL,
[nvarchar](255) NULL,
[MT] [nvarchar](255) NULL
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[HSN_New](
[MSN] [float] NULL,
[SN] [nvarchar](255) NULL,
[ID] [int] IDENTITY(1,1) NOT NULL,
[Current] [bit] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[HSN] ([MSN], [SN], [RN], [DQ], , [MT]) VALUES (99881, N'Alpharetta West', N'A Central', N'Ron Burgandy', N'rburgandy@test.com', N'LSD-HIJ-ABC')
GO
INSERT [dbo].[HSN] ([MSN], [SN], [RN], [DQ], , [MT]) VALUES (33221, N'Mobile East', N'A Central', N'Ron Burgandy', N'rburgandyd@test.com', N'RLD-HDL-BCS')
GO
SET IDENTITY_INSERT [dbo].[HSN_New] ON
GO
INSERT [dbo].[HSN_New] ([MSN], [SN], [ID], [Current]) VALUES (99881, N'Alpharetta West', 1, 1)
GO
INSERT [dbo].[HSN_New] ([MSN], [SN], [ID], [Current]) VALUES (33221, N'Mobile East', 2, 1)
GO
I just inherited a database from a team member who resigned unexpectedly and this database is a nightmare. The table structure is non existent and while what was originally set-up "works" - it is not easily understood nor "correct".
For example, this is DDL for two tables - that I feel should actually be broken down into multiple smaller tables, what do you guys think here? For example, we have the table HSN New which duplicates data from HSN, but has an active column to show if the info is active or not. To me the best set-up would be to have a table for MSN, SN (which is master site name, site name, and active). Then have a table with a Auto-Number PK and a FK that links back to MSN that has (ID, FKMSN, RN), Then have a table with a Auto-Number PK and a FK for the 'other' non connected data (ID, FKMSN, DQ, Email), and lastly a table for the MT - set-up like this (ID, FKMSN, MT). Essentially everything links back to the MasterStoreNumber in the what I'll call Main table. This is how the current DDL is set-up, I know it's a cluster. What do you guys think?
March 2, 2019 at 1:52 pm
For starters, I would rename the columns so they mean something. Databases where the column names are just about meaningless (and have the wrong data types) are a nightmare to work with. I love an obvious design - so I can spend my energy doing something useful, not trying to figure out someone's arcane naming conventions.
Oh right... should I restructure? Without an explanation of what the columns mean, it's impossible to tell. (That and sample data).
March 4, 2019 at 10:17 am
If you want to start over on the design, then start over on the logical design process. The existing table has no relevance to that process.
Lay out the entities and attributes (i.e. logical objects, not physical ones) and go thru the normal logical design process, just as you would for any other data. Create the best design you can from there. If it happens to match the existing structure, fine, stick with what is there. If no, implement the new design.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply