So I downloaded the Adventureworks 2012 SQL database to browse hoping to find something interesting and just nearly fell asleep looking at example data when I came across something new or that I at least never noticed before...a field with a data type of "xml(CONTENT Person.AdditionalContactInfoSchemaCollection)" and when viewing the example data I see things like:
<AdditionalContactInfo xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo" xmlns:crm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactRecord" xmlns:act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes"> <act:telephoneNumber> <act:number>425-555-1112</act:number> <act:SpecialInstructions>Call only after 5:00 p.m.</act:SpecialInstructions> </act:telephoneNumber>Note that the customer has a secondary home address.<act:homePostalAddress><act:Street>123 Oak</act:Street><act:City>Seattle</act:City><act:StateProvince>WA</act:StateProvince><act:PostalCode>98001</act:PostalCode><act:CountryRegion>USA</act:CountryRegion><act:SpecialInstructions>If correspondence to the primary address fails, try this one.</act:SpecialInstructions></act:homePostalAddress>Customer provided additional email address.<act:eMail><act:eMailAddress>email@example.com</act:eMailAddress><act:SpecialInstructions>For urgent issues, do not send e-mail. Instead use this emergency contact phone<act:telephoneNumber><act:number>425-555-1111</act:number></act:telephoneNumber>.</act:SpecialInstructions></act:eMail><crm:ContactRecord date="2001-06-02Z">This customer is interested in purchasing high-end bicycles for his family. The customer contacted Michael in sales.</crm:ContactRecord></AdditionalContactInfo>
And it got me thinking...is Microsoft advocating using xml tags in database fields instead of setting up 1-n relationships?
The example above could be split into a 1-n relationship on all of the xml tags, phone numbers, special instructions, address information, and other non-specific comments.
I hear the nay-sayers and xml lovers and abusers now saying that splitting such information up among many different tables creates a tangled web of relationships and dependancies which can be a nightmare to follow; and I will concede to that to a point, but coming from a database with probably over 50 relationships already, why opt for a custom data type that:
- bloats the field size to accomodate xml markup,
- requires additional parsing by the application receiving the data,
- clogs network wires with extra data that when the application is finished parsing may not even be necessary,
- makes the data nearly impossible (unless I am mistaken) to filter in select statements and,
- requires the dba and future dbas to understand the data type that was defined.
Am I missing something here or is this clearly a terrible use of xml markup (as if there even is a good use for xml markup...)? I would think tables named TelephoneNumbers, SpecialInstructions, and Addresses with a 1-n relationship to the Person table would be MUCH easier to understand and follow than an obscure, poorly named (AdditionalContactInfo) field is.
Does anyone know why a dba would do something like this?
I'm really curious to see if anyone would do this in reality and if so, why?
Edited 4 Years Ago by zachattack05: Grammar