April 3, 2014 at 12:07 am
Hello All
While working with a vast variety of support projects, i find a sql design where all the fields in a single form (say about 100 fields which are dump data as they are not related to any reports and searching criteria) are dumped in a sql database column in a XML format. See below an example
<?xml version="1.0" encoding="utf-8"?><FormBuilder><ClientID>0</ClientID><SiteID>0</SiteID><IncidentType></IncidentType><IncidentCategory></IncidentCategory><IncidentSubCategory></IncidentSubCategory><CreatedBy>2</CreatedBy><CreatedOn>Wednesday, April 02, 2014</CreatedOn><ModifiedOn /><ModifiedBy /><Section SectionID="ASD" SectionDisplayName="ASD" ColumnType="1" IsDeleted="0" SectionPosition="1"><SectionField FieldName="Bro" Section="ASD" ModuleID="0" Length="" PickData="" ChkData="" RadioData="" ListData="" FieldType="Text" Checked="false" ColumnType="1" IsDeleted="0" CoulmnOrder="0" FieldID="1" IsPrimary="" IsMandatory="" SystemMandatory="" RowPosition="1" FullRow="" /></Section></FormBuilder>
Just want to know the comments how far is this design feasible.....
What are the pros and cons of such a design...
Where we should use such type of db design where are the fields are dumped in a single column...
April 3, 2014 at 12:53 am
Pro: flexible
Cons: Performance, no data integrity, misuse of a relational structure (sounds like they want a schema-less database, one of the NoSQL variants), hard to manage
When you would use it: Just about never I would hope.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 3, 2014 at 4:38 am
It's an approach taken by a developer or development team that doesn't understand SQL Server, or, just didn't want to take the time to do things correctly. It's very fast for programming because it's very simple. But it's not going to be good for SQL Server for just about anything, as Gail said.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 3, 2014 at 7:42 am
Grant Fritchey (4/3/2014)
It's an approach taken by a developer or development team that doesn't understand SQL Server, or, just didn't want to take the time to do things correctly. It's very fast for programming because it's very simple. But it's not going to be good for SQL Server for just about anything, as Gail said.
So are we saying store the xml in a text file and keep it out of the database, or don't use xml? Because xml seems to be used quite a bit.
April 3, 2014 at 8:09 am
Just to play the devil's avocado:
- XML is a first-class citizen in SQL Server and as far as validation goes that's what DTDs are for. Right?
- I'm not sure what the benefit would be for the programmers to go through an ORM layer to translate arbitrary XML data into a column format, if that's not how they're going to use it.
April 3, 2014 at 8:18 am
Cody K (4/3/2014)
Just to play the devil's avocado:- XML is a first-class citizen in SQL Server and as far as validation goes that's what DTDs are for. Right?
- I'm not sure what the benefit would be for the programmers to go through an ORM layer to translate arbitrary XML data into a column format, if that's not how they're going to use it.
I'm biased toward this sort of thinking, I bet they have a module that consumes xml, they have a module that generates xml, they didn't for whatever reason want to regenerate this xml each time it needed to be consumed, and their project required a database anyways, so why not borrow a column to stash it instead of spending an extra text file that you now have to be able to find, etc...
April 3, 2014 at 8:33 am
Cody K (4/3/2014)
Just to play the devil's avocado:- XML is a first-class citizen in SQL Server and as far as validation goes that's what DTDs are for. Right?
- I'm not sure what the benefit would be for the programmers to go through an ORM layer to translate arbitrary XML data into a column format, if that's not how they're going to use it.
No argument at all with the last point. It really does depend on the use. Maybe should have asked that question up front. If we're just storing the data to store it, fine. It's when you start trying to query it like it's structured data that things begin to go south, sometimes very quickly depending on the types of queries. Back to the intended use again though.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 3, 2014 at 8:34 am
patrickmcginnis59 10839 (4/3/2014)
Grant Fritchey (4/3/2014)
It's an approach taken by a developer or development team that doesn't understand SQL Server, or, just didn't want to take the time to do things correctly. It's very fast for programming because it's very simple. But it's not going to be good for SQL Server for just about anything, as Gail said.So are we saying store the xml in a text file and keep it out of the database, or don't use xml? Because xml seems to be used quite a bit.
I made a silly assumption that the data is relational in nature. If it is, my statement is still accurate. If it's not, I'm an idiot.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 3, 2014 at 10:04 am
Cody K (4/3/2014)
- I'm not sure what the benefit would be for the programmers to go through an ORM layer to translate arbitrary XML data into a column format, if that's not how they're going to use it.
If the only thing that's going to be done with the XML is store it as a unit and retrieve it as a unit, fine.
If any form of search or aggregation or ordering or processing in the database, then the data should be stored in a relational form (or a non-relational solution should be considered instead)
The old 'appropriate tool for the job'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 2, 2014 at 1:43 am
Hello there
Sorry for the late reply...
Yeah, they are dumping the data means the data that is being not used anywhere in search, filtering etc etc are used as dump in XML format (with their HTML structure and values, i mean a complete html form in XML format) and stored in single column.
I know that there are some cons of this design but want to know about
1. Bandwidth while connecting with SQL
2. Performance -- Fast OR slow
3. Flexibility
Thanks for everything
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply