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,


 
LVL 7
CboudrozAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<What's the best solution and why?>>
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.
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<It is based on traditional fundamental rules of database science that worked in the last 4 years.>>
Sorry I meant 40 years...
CboudrozAuthor Commented:
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.


Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<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...
CboudrozAuthor Commented:
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)
 


Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<but let say I'm working whit a web application, did you think that the web should used XML to transfer  the schedule?>>
My comment concerned primarily storage or design related issues.  As far as how the data needs to be *presented*, as an output, to some other application, or as a glue due to specific technical requirements for data to be serialized, that really is a matter of how much control you have over the entire process.  

My rule of thumb is simply: stay away from XML as much as possible.  There is pretty much *nothing* that you can do using XML that you can't do better without using it .  Including, the example the mentionned

The only case where I see XML as necessary is if one *must* serialize the output of a query to be sent to an API that imposes it.  Makes the choice obvious doesn't it?

HTH

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CboudrozAuthor Commented:
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,
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Glad I could help..:)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.