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 Wednesday, October 17, 2012 9:46 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:07 PM
Points: 234, Visits: 2,145
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.
Post #1374126
Posted Thursday, October 18, 2012 2:04 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, October 10, 2014 1:07 AM
Points: 692, Visits: 2,803
Hi

Just my 2 cents but if you have the scope I would suggest normalising this table and splitting all of the related data groups into separate table(s) rather than try and work around an initial design that is flawed..

Andy


==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Post #1374187
Posted Thursday, October 18, 2012 7:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 2, 2014 8:32 AM
Points: 20, Visits: 112
Out of curiosity is this database for an application that allows users to create/define custom fields or even data types? The only reason I'm asking is because I've had to support such applications which users love because they can create there own custom forms but it can be a pain on the database side. If that is the case you are on the right path using a name/value pair. Just make sure you index your table properly. Otherwise you'll save yourself many headaches by moving the data to proper tables.
Post #1374350
Posted Thursday, October 18, 2012 7:35 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:07 PM
Points: 234, Visits: 2,145
The users cant create columns or types by themselves but they can request them to be created (and they are). So it sounds very similar to what you have done yb751.

Please see below for a simplified version of the current table (names have been changed).
Last lot of changes required adding col21 - col40 due to the fact the users "ran out" of columns.
As mentioned before im open to any suggestions on a better way of doing this.

CREATE TABLE [dbo].[#Table1](
[#Table1ID] [int] NOT NULL,
[Year] [float] NULL,
[Period] [int] NULL,
[Period_2] [int] NULL,
[Period_3] [int] NULL,
[Period_4] [int] NULL,
[Period_5] [int] NULL,
[Type_1] [float] NULL,
[Type_2] [float] NULL,
[Type_3] [float] NULL,
[Type_4] [float] NULL,
[Type_5] [float] NULL,
[Type_6] [float] NULL,
[Col1] [float] NULL,
[Col2] [float] NULL,
[Col3] [float] NULL,
[Col4] [float] NULL,
[Col5] [float] NULL,
[Col6] [float] NULL,
[Col7] [float] NULL,
[Col8] [float] NULL,
[Col9] [float] NULL,
[Col10] [float] NULL,
[Col11] [float] NULL,
[Col12] [float] NULL,
[Col13] [float] NULL,
[Col14] [float] NULL,
[Col15] [float] NULL,
[Col16] [float] NULL,
[Col17] [float] NULL,
[Col18] [float] NULL,
[Col19] [float] NULL,
[Col20] [float] NULL,
[Col21] [float] NULL,
[Col22] [float] NULL,
[Col23] [float] NULL,
[Col24] [float] NULL,
[Col25] [float] NULL,
[Col26] [float] NULL,
[Col27] [float] NULL,
[Col28] [float] NULL,
[Col29] [float] NULL,
[Col30] [float] NULL,
[Col31] [float] NULL,
[Col32] [float] NULL,
[Col33] [float] NULL,
[Col34] [float] NULL,
[Col35] [float] NULL,
[Col36] [float] NULL,
[Col37] [float] NULL,
[Col38] [float] NULL,
[Col39] [float] NULL,
[Col40] [float] NULL,
CONSTRAINT [PK_#Table1] PRIMARY KEY CLUSTERED
(
[#Table1ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Post #1374624
Posted Friday, October 19, 2012 8:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 2, 2014 8:32 AM
Points: 20, Visits: 112
Ok, couple more questions then.

You have a field called Year but are using a float datatype. I would suggest you use DATETIME types for any fields relating to well dates... Once in that format there is many ways to break it down and get what you need when querying the table. i.e. DATEPART(yyyy, mydatefield) AS year

Also do you need to use float in col1 to col 40? I'm not saying there is never a need for that datatype but it can be a little tricky. I'm assuming you need decimal places so DECIMAL or NUMERIC might be better to use in your situation. However you obviously know your requirements but here is a little reading on the matter.
http://msdn.microsoft.com/en-us/library/ms187912%28v=sql.105%29.aspx

Also even if you create a "generic" table like you proposed make sure it's still contain relevant data to AN object. Don't try to jam every little field a user wants added to one table. As an example one of the said application I used to support allowed users to create forms and define there own fields right in the application. Fields would be added while some would be removed (but not delete from the database for reporting purposes). So to make that happen the database needed to be flexible. However not all custom fields were added to the same table. The database itself contained 3-4 of these types of tables with appropriately grouped data.
Post #1374861
Posted Friday, October 19, 2012 9:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 2, 2014 8:32 AM
Points: 20, Visits: 112

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.


My apologies...based on your sample code above it appears like you already want to convert those float values to decimal. A timestamp field could also be helpful depending on how the table will be used. This is of course if it isn't arleady being captured in another table.
Post #1374875
Posted Friday, October 19, 2012 7:52 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:07 PM
Points: 234, Visits: 2,145
Yeah i have the datatypes sorted out thanks Timestamps arent needed. We arent doing anything timebased apart from the "Years" column which is actually more like "Periods" which will always start at 1 and go to N.
I am interested however on the joining aspects as currently most fields are in one big table (one big table multiple times which means using dynamic sql and passing table names around).
Im trying to find a solution that will at least alleviate some of the dynamic sql pain and make the code easier to maintain. I can split the col1 ...col40 into smaller subsets and can put them into mulitple tables. Im still a bit unsure if this is the right approach or not. What this would mean though is i might need to join on one table for a project and not for another. Are such dynamic joins recommended at all ? Any other options ?

Im also using the histogram chart code i found on this site (a few minor tweeks) which takes in one column for the data. The users have said they want to use *any* column as a histogram input. Is there any way apart from dynamic sql that i can accomplish this ?
Thanks for all the help so far as i do understand im pretty average at design currently.
Post #1375074
Posted Sunday, October 21, 2012 11:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:06 PM
Points: 7,125, Visits: 12,720
I agree with Andy. What you have now is what I call the 'spreadsheet approach' to data modeling where everything is in one wide table. You said one column can mean different things based on the value of another column in the row (you called it a "project"), i.e. a column's value can represent an entity from a different domain than a value in the same column from another row. That approach violates first normal form.

I would also decompose the wide, multi-purpose table into many tables that represent the domains of data you are managing. The approach you have devised to replace the 'spreadsheet approach' is known as an EAV and should only be used in rare, niche cases.

Keeping it Simple: EAV FAIL



I am interested however on the joining aspects as currently most fields are in one big table (one big table multiple times which means using dynamic sql and passing table names around). Im trying to find a solution that will at least alleviate some of the dynamic sql pain and make the code easier to maintain. I can split the col1 ...col40 into smaller subsets and can put them into mulitple tables. Im still a bit unsure if this is the right approach or not. What this would mean though is i might need to join on one table for a project and not for another. Are such dynamic joins recommended at all ? Any other options ?

What you are struggling with is the impedance mismatch between object-oriented thinking and relational modeling. In short, model your data per the relational model in the database (3NF is a good place to shoot for to begin) and abstract the impedance mismatch in your application layer, i.e. do not impose on the database to handle the abstraction because frankly it is not very good at it and you'll end up devolving your codebase doing backflips with dynamic SQL, self-joins or scalar UDFs.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1375186
Posted Sunday, October 21, 2012 12:34 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 6:45 AM
Points: 35,366, Visits: 31,902
matak (10/18/2012)
The users cant create columns or types by themselves but they can request them to be created (and they are). So it sounds very similar to what you have done yb751.

Please see below for a simplified version of the current table (names have been changed).
Last lot of changes required adding col21 - col40 due to the fact the users "ran out" of columns.
As mentioned before im open to any suggestions on a better way of doing this.

CREATE TABLE [dbo].[#Table1](
[#Table1ID] [int] NOT NULL,
[Year] [float] NULL,
[Period] [int] NULL,
[Period_2] [int] NULL,
[Period_3] [int] NULL,
[Period_4] [int] NULL,
[Period_5] [int] NULL,
[Type_1] [float] NULL,
[Type_2] [float] NULL,
[Type_3] [float] NULL,
[Type_4] [float] NULL,
[Type_5] [float] NULL,
[Type_6] [float] NULL,
[Col1] [float] NULL,
[Col2] [float] NULL,
[Col3] [float] NULL,
[Col4] [float] NULL,
[Col5] [float] NULL,
[Col6] [float] NULL,
[Col7] [float] NULL,
[Col8] [float] NULL,
[Col9] [float] NULL,
[Col10] [float] NULL,
[Col11] [float] NULL,
[Col12] [float] NULL,
[Col13] [float] NULL,
[Col14] [float] NULL,
[Col15] [float] NULL,
[Col16] [float] NULL,
[Col17] [float] NULL,
[Col18] [float] NULL,
[Col19] [float] NULL,
[Col20] [float] NULL,
[Col21] [float] NULL,
[Col22] [float] NULL,
[Col23] [float] NULL,
[Col24] [float] NULL,
[Col25] [float] NULL,
[Col26] [float] NULL,
[Col27] [float] NULL,
[Col28] [float] NULL,
[Col29] [float] NULL,
[Col30] [float] NULL,
[Col31] [float] NULL,
[Col32] [float] NULL,
[Col33] [float] NULL,
[Col34] [float] NULL,
[Col35] [float] NULL,
[Col36] [float] NULL,
[Col37] [float] NULL,
[Col38] [float] NULL,
[Col39] [float] NULL,
[Col40] [float] NULL,
CONSTRAINT [PK_#Table1] PRIMARY KEY CLUSTERED
(
[#Table1ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]



What are all the ColXX colums used for? Depending on the answer to that question, the use of a Name/Value table could be the worst idea or the best.

Also, why are they all FLOAT?


--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."

(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 #1375196
Posted Sunday, October 21, 2012 7:32 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:07 PM
Points: 234, Visits: 2,145
OK so yes originally this was based on spreadsheets (before my time) and i dont want to use an EAV but I lack design skills which is why im asking here for any sort of advice.
The industry im dealing with is mining and no mine is the same. The ColXX are able to be mapped by the user via a mapping table to describe what they are. So for one project Col1 may be Iron grade (iron ore mine) or Gold grade (gold mine) or any other sort of metal you can think of based on mining. Also, each type of mine can have different numbers of ColXX. I would love to be able to use IronGrade, GoldGrade as a column name but since they will never be related im unsure on how to go about doing this without using either dynamic sql or customising code for each mine.

The table i posted with ColXX is part of the current structure - the new structure will be using different data types. This is one thing I believe I can do after working with the data for the past few weeks

you'll end up devolving your codebase doing backflips with dynamic SQL

The system is currently purely dynamic with columns and tables being passed around I would do anything to stop this as its extremely difficult to maintain.


If anything else needs clarifying please let me know and i will do my best to provide more information.
Post #1375230
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse