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 ««123»»

Redesign help Expand / Collapse
Author
Message
Posted Sunday, October 21, 2012 7:52 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
What I think you are after is some guidance creating a data model that supports the industry-data you're trying to support, mining. I tried searching the internet for "standard mining data model" and that combination tends to turn up lots of pages related to "data mining." I did find some ArcGIS standard data models, but not knowing your business I am not sure if those are relevant. At any rate, finding a data model that does not make use of EAV and does not compel you to write dynamic SQL every time you wish to access data for a given project, yet at the same time flexibly supports your line of business may be worth the effort. If you do not find a standard data model you can immediately pickup and use you may at least get some ideas about how others are modeling their mining data.

As an alternative to EAV you might think about using sparse tables (table per project) or sparse columns (one table for all projects however each column contains data for one data domain) to ease the burden. At least that way you can get back to 1NF and stop re-purposing generically named columns that change meaning depending on the row (i.e. project) being accessed.

I just found this article earlier in the week and it is the second time I have passed it along:

And the EAV winner is .... sparse columns by Bob Beauchemin


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1375234
Posted Sunday, October 21, 2012 7:55 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:21 PM
Points: 35,951, Visits: 30,235
A "Snowflake" structure comes into mind here but I believe this is too large a task to do via a forum. Have you considered bringing in a consultant architect to try to straighten this out? Don't forget that anything that currently touches this table will also need to be reengineered for the new structure.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1375235
Posted Sunday, October 21, 2012 7:58 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:06 AM
Points: 199, Visits: 1,554
Thanks for the advice about sparse tables and columns - ive never used them before so i will do some reading.
Post #1375236
Posted Sunday, October 21, 2012 8:00 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 1:06 AM
Points: 199, Visits: 1,554
I have considered a consultant yes and will talk to the appropriate people about getting one in.
I thought id give the forums a go just in case it was something not too hard (im sure ive mentioned my lack of design skills !! )
Post #1375238
Posted Sunday, October 21, 2012 8:23 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
matak (10/21/2012)
Thanks for the advice about sparse tables and columns - ive never used them before so i will do some reading.

You're welcome.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1375240
Posted Monday, October 22, 2012 7:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 12:30 PM
Points: 20, Visits: 105
matak (10/21/2012)
I have considered a consultant yes and will talk to the appropriate people about getting one in.
I thought id give the forums a go just in case it was something not too hard (im sure ive mentioned my lack of design skills !! )


Unfortuntely there is no definative solution that can be suggested in the context of this forum without seeing the whole picture. If your company is willing to pay for a consultant that would probably be the best thing for you. You could have somebody to help you out every step of the way. Of course you might have to become a salesman as to why? Make a case and try to use current issues to help you with that. Are you experiencing peformance problems and so on...

Good Luck!
Post #1375433
Posted Monday, October 22, 2012 6:19 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
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 @UserID AS INT
SET @UserID = 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 = @UserID
) 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


Post #1375794
Posted Monday, October 22, 2012 8:31 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 7,070, Visits: 12,523
This is just a pared-down example of a method used by one of the largest open-source development platforms in existence.

And that platform would be...care to share?


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1375807
Posted Tuesday, October 23, 2012 10:52 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 11:01 AM
Points: 1,468, Visits: 4,267
Instead of separate denormalized columns, I'd rather contain user defined attributes in an XML column, preferably in a seperate extended type table. In the example below, each extended type (regular, contractor, temp, etc.) would have a seperate extype_id, and an employee could be of multiple types. Users can create their own types and dump their junk in it without cluttering up and fragmenting an otherwise normalized primary table. You can even create a view (or views) that join the two tables together, providing the users with a denormalized flat resultset that's easy for them to query and visualize like an Excel sheet.

create table employee
(
employee_id smallint not null primary key,
ssn char(12) not null,
hire_date datetime not null,
reportsto_id smallint null
);

create table employee_extype
(
primary key (employee_id, extype_id ),
employee_id smallint not null,
extype_id smallint not null,
exttype_xml XML null
);




"Winter Is Coming" - April 6, 2014
Post #1376173
Posted Tuesday, October 23, 2012 11:05 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
opc.three (10/22/2012)
This is just a pared-down example of a method used by one of the largest open-source development platforms in existence.

And that platform would be...care to share?

DotNetNuke

DotNetNuke Users List

...and no, I don't work for DNN. But it is our primary development platform.

 
Post #1376177
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse