Cboudroz
asked on
MS SQL - Normalize VS XML
Hi,
I'm still new to usage of XML in database environnement (SQL 2005) and I'm wondering what's the best practices.
I have one table for user Event. They can schedule a event to be run many time during a day.
Ex: run at 8AM, 11AM, and 5pm
To save the schedule I see two different solution create table to save the hours (1 row by time) and link it to event table or used XML column in the Event table, to save the hours in XML collection.
What's the best solution and why?
can both solution be good in different case?
thanks,
I'm still new to usage of XML in database environnement (SQL 2005) and I'm wondering what's the best practices.
I have one table for user Event. They can schedule a event to be run many time during a day.
Ex: run at 8AM, 11AM, and 5pm
To save the schedule I see two different solution create table to save the hours (1 row by time) and link it to event table or used XML column in the Event table, to save the hours in XML collection.
What's the best solution and why?
can both solution be good in different case?
thanks,
<<It is based on traditional fundamental rules of database science that worked in the last 4 years.>>
Sorry I meant 40 years...
Sorry I meant 40 years...
ASKER
Thanks for the reply I think its the best answer I can get.
You are a real "Sage".
I will try to avoid using XML in my database.
You are a real "Sage".
I will try to avoid using XML in my database.
<<I will try to avoid using XML in my database.>>
Yes. Unless one likes to go through unnecessary pain, XML storage is to be ran away from in anything database related. I mean fast.
Hope this helps...
Yes. Unless one likes to go through unnecessary pain, XML storage is to be ran away from in anything database related. I mean fast.
Hope this helps...
ASKER
yes, it help, you kind of confirm what I was thinking.
but let say I'm working whit a web application, did you think that the web should used XML to transfer the schedule?
Ex:
if a user try to insert 10 new schedule for one event
1- the web application can call a SP once whit XML parameter that will include the 10 date, but in my SP will saved the data in a normalize way (10 rows inserted in table Event_Schedule). And if web application need to access these data I will send back the data in XML using a FOR XML SELECT statement.
or
2- The web application call 10 times my SP whit each time different time in parameter (datetime param). and if needed to access the data I will return a normal dataset (select inside SP)
but let say I'm working whit a web application, did you think that the web should used XML to transfer the schedule?
Ex:
if a user try to insert 10 new schedule for one event
1- the web application can call a SP once whit XML parameter that will include the 10 date, but in my SP will saved the data in a normalize way (10 rows inserted in table Event_Schedule). And if web application need to access these data I will send back the data in XML using a FOR XML SELECT statement.
or
2- The web application call 10 times my SP whit each time different time in parameter (datetime param). and if needed to access the data I will return a normal dataset (select inside SP)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok, I think I can close that discussion,
I was trying to see if XML in SQL 2005 will change the best practices regarding data manipulation, but in reality it's only a add-on for particular case when we work whit 3rd party tool that require XML.
I have a complete control on the Web application development, so after that discussion, the only real advantage I see in using XML in MS SQL 2005, it's to convert output to another tool that need XML
Example Email (convert select in HTML):
SET @HTML = CONVERT(NVARCHAR(MAX), (SELECT TestType as td,'', TestName as td, '',TestResults as td FROM tempdb..UnitTestResults FOR XML PATH('tr'), ELEMENTS))
The information provide was really what I need,
thanks for your help,
I was trying to see if XML in SQL 2005 will change the best practices regarding data manipulation, but in reality it's only a add-on for particular case when we work whit 3rd party tool that require XML.
I have a complete control on the Web application development, so after that discussion, the only real advantage I see in using XML in MS SQL 2005, it's to convert output to another tool that need XML
Example Email (convert select in HTML):
SET @HTML = CONVERT(NVARCHAR(MAX), (SELECT TestType as td,'', TestName as td, '',TestResults as td FROM tempdb..UnitTestResults FOR XML PATH('tr'), ELEMENTS))
The information provide was really what I need,
thanks for your help,
Glad I could help..:)
Normalization is the correct way to store and optimize data retrieval. It is based on traditional fundamental rules of database science that worked in the last 4 years. XML storage or structure is underoptimized for access, does not guarantee data integrity, and does nothing more than create problems in a database perpsective . It is based on vacuum.
<<can both solution be good in different case?>>
They can't be compared. XML is a more of a solution looking for a problem in database world. Using it usually creates a total mess.