matak (10/17/2012)
Hi,Ive finally been allowed to redesign some tables which are "wide" (Think Field1, Field2, Field3 ... Fieldn). There are multiple fields that do this and columns get added when more Fields are needed.
To make it even better, Field1 for one project may not contain the same information as Field1 for a different project - there is a "mapping" table that lets people know what the field actually means.
What im thinking of is to split these columns into a separate table something like the below
IF OBJECT_ID('TempDB..#Field','U') IS NOT NULL
DROP TABLE #Field
CREATE TABLE #Field
(
fieldID int NOT NULL,
pkFromOrigTable int NOT NULL,
fieldName varchar(200) not null,
fieldValue decimal(30,20)
)
Any advice, suggestions are more than welcome. I want to try and avoid creating something as bad.
Below I've re-created in simplified form some tables and data that allow unlimited creation of custom columns that can be keyed to a sample users table. This is just a pared-down example of a method used by one of the largest open-source development platforms in existence.
/* Pivot Query to display Profile Properties */
/* Note: Scripts to create and populate test tables below */
DECLARE @user-id AS INT
SET @user-id = 1
SELECT
*
FROM
(
SELECT
pd.Name AS PropertyName
,up.PropertyValue AS PropertyValue
FROM
dbo.TEST_Users AS u
INNER JOIN dbo.TEST_UserProfile AS up
ON up.UserID = u.userid
INNER JOIN dbo.TEST_ProfileDefinition AS pd
ON up.DefinitionID = pd.DefinitionID
WHERE
u.UserID = @user-id
) AS Result PIVOT
(MAX(PropertyValue) FOR PropertyName IN
([FirstName],[LastName],[StreetAddress],[City],[State],[Zip],[FavoriteColor],[PIN]))
AS UserProfile
/* Run Once */
CREATE TABLE [dbo].[TEST_Users](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[Username] [nvarchar](100) NOT NULL,
CONSTRAINT [PK_Test_Users] PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[TEST_Users] ON
INSERT [dbo].[TEST_Users] ([UserID], [Username]) VALUES (1, N'George Washington')
INSERT [dbo].[TEST_Users] ([UserID], [Username]) VALUES (2, N'John Adams')
INSERT [dbo].[TEST_Users] ([UserID], [Username]) VALUES (3, N'Thomas Jefferson')
INSERT [dbo].[TEST_Users] ([UserID], [Username]) VALUES (4, N'James Madison')
SET IDENTITY_INSERT [dbo].[TEST_Users] OFF
CREATE TABLE [dbo].[TEST_UserProfile](
[ProfileID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[DefinitionID] [int] NOT NULL,
[PropertyValue] [nvarchar](max) NULL,
CONSTRAINT [PK_TEST_UserProfile] PRIMARY KEY CLUSTERED
(
[ProfileID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[TEST_UserProfile] ON
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (1, 1, 1, N'George')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (2, 1, 2, N'Washington')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (3, 1, 3, N'123 Main St')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (4, 1, 4, N'Mt Vernon')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (5, 1, 5, N'VA')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (6, 1, 6, N'34567')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (7, 1, 7, N'Blue')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (8, 1, 8, N'1776')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (10, 2, 1, N'John')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (11, 2, 2, N'Adams')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (12, 2, 3, N'456 Elm Ln')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (13, 2, 4, N'Boston')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (14, 2, 5, N'MA')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (15, 2, 6, N'01234')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (16, 2, 7, N'Red')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (17, 2, 8, N'1799')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (18, 3, 1, N'Thomas')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (19, 3, 2, N'Jefferson')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (20, 3, 3, N'RR 567')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (21, 3, 4, N'Charlottesville')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (22, 3, 5, N'VA')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (23, 3, 6, N'23456')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (24, 3, 7, N'Green')
INSERT [dbo].[TEST_UserProfile] ([ProfileID], [UserID], [DefinitionID], [PropertyValue]) VALUES (25, 3, 8, N'1801')
SET IDENTITY_INSERT [dbo].[TEST_UserProfile] OFF
CREATE TABLE [dbo].[TEST_ProfileDefinition](
[DefinitionID] [int] IDENTITY(1,1) NOT NULL,
[Category] [nvarchar](50) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[DataType] [nvarchar](50) NOT NULL,
[Length] [int] NOT NULL,
[ViewOrder] [int] NOT NULL,
CONSTRAINT [PK_TEST_ProfileDefinition] PRIMARY KEY CLUSTERED
(
[DefinitionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[TEST_ProfileDefinition] ON
INSERT [dbo].[TEST_ProfileDefinition] ([DefinitionID], [Category], [Name], [DataType], [Length], [ViewOrder]) VALUES (1, N'Personal', N'FirstName', N'varchar', 50, 10)
INSERT [dbo].[TEST_ProfileDefinition] ([DefinitionID], [Category], [Name], [DataType], [Length], [ViewOrder]) VALUES (2, N'Personal', N'LastName', N'varchar', 50, 20)
INSERT [dbo].[TEST_ProfileDefinition] ([DefinitionID], [Category], [Name], [DataType], [Length], [ViewOrder]) VALUES (3, N'Address', N'StreetAddress', N'varchar', 50, 10)
INSERT [dbo].[TEST_ProfileDefinition] ([DefinitionID], [Category], [Name], [DataType], [Length], [ViewOrder]) VALUES (4, N'Address', N'City', N'varchar', 20, 20)
INSERT [dbo].[TEST_ProfileDefinition] ([DefinitionID], [Category], [Name], [DataType], [Length], [ViewOrder]) VALUES (5, N'Address', N'State', N'char', 2, 30)
INSERT [dbo].[TEST_ProfileDefinition] ([DefinitionID], [Category], [Name], [DataType], [Length], [ViewOrder]) VALUES (6, N'Address', N'Zip', N'varchar', 20, 40)
INSERT [dbo].[TEST_ProfileDefinition] ([DefinitionID], [Category], [Name], [DataType], [Length], [ViewOrder]) VALUES (7, N'User', N'FavoriteColor', N'varchar', 50, 10)
INSERT [dbo].[TEST_ProfileDefinition] ([DefinitionID], [Category], [Name], [DataType], [Length], [ViewOrder]) VALUES (8, N'User', N'PIN', N'int', 4, 20)
SET IDENTITY_INSERT [dbo].[TEST_ProfileDefinition] OFF