SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Best Practice Help - Table Structures


Best Practice Help - Table Structures

Author
Message
patrickmcginnis59 10839
patrickmcginnis59 10839
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19065 Visits: 7881
amy26 - Tuesday, September 11, 2018 10:29 AM
Sorry, the argument isn't over storing it as XML ... which was perhaps an explanation error on my part. The argument is just over a single table with a blob field storing every value for a data element using string based values instead of keys etc. They do put some XML into the blob field...but most of the values are just straight characters. It is also pure laziness and no regard for proper design. Especially after having me (for months now) labor to create all the said proper tables and then cause one developer complained "boo hoo I have to change my code", he apparently had enough clout to have it remain as is. I don't really care if the data is stored as XML or not, its just this single table thing that is going to get SUPER huge and some of our data records are one to many relationships... so not quite sure how the hell they plan to handle that.

Are you telling me you spent months designing tables in isolation from your developers? Maybe we should drop the design questions here and you could go into more detail on the process you're burdened with operating within. How did they finish code that they cannot now change, if the tables weren't done? Clearly there should have been some agreement on table structure before either of you started, why didn't that happen?

to properly post on a forum:http://www.sqlservercentral.com/articles/61537/
Alan Burstein
Alan Burstein
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53634 Visits: 8955
amy26 - Friday, September 7, 2018 4:17 PM
Hi there, I am being asked to provide a list of all the reasons that we should create actual normalized tables with data fields vice creating one table with a blob column that stores all sorts of data elements in one field as XML. I can’t believe I actually have to do this but wanted some help with my list to make sure I am not forgetting anything or off base. This is for a system that is heavy on database logic and data manipulation. It is also a transactional system that pushes data to other systems and ingests data.Pros of normalized rational tablesBigGrinata integrity Data validation capabilities Querability Query performanceACIDSystem to system interfaces (having like data)Ease of understanding Easier object-to-data mappingSome of my topics may overlap each other but I plan on fleshing it all out and describing each area better. Just wanted to get my thoughts down and then have some community input. I don’t have any other dbas to bounce ideas off of ... it’s just me! Smile

Just to pile on about how bad this is (and I know this was not your idea) starting with this sample data:
IF OBJECT_ID('dbo.table1','U') IS NOT NULL DROP TABLE dbo.table1;
IF OBJECT_ID('dbo.table2','U') IS NOT NULL DROP TABLE dbo.table2;

CREATE TABLE dbo.table1 (nameId INT IDENTITY, phoneNbr VARCHAR(20), email VARCHAR(100));
CREATE TABLE dbo.table2 (nameId INT IDENTITY, ContactInfo XML);

INSERT dbo.table1(phoneNbr,email) VALUES ('555-444-3333','JoeBlow@gmail.com');
INSERT dbo.table2(ContactInfo) VALUES
('<contactInfo>
<phone>555-444-333</phone>
<email>JoeBlow@gmail.com</email>
</contactInfo>');


Note these queries:
-- relational
SELECT t.nameId, t.phoneNbr, t.email
FROM dbo.table1 AS t
WHERE t.email IS NOT NULL

-- XML:
SELECT
t.nameId,
phoneNbr = t.ContactInfo.value('(contactInfo/phone/text())[1]', 'VARCHAR(20)'),
email = t.ContactInfo.value('(contactInfo/email/text())[1]', 'VARCHAR(100)')
FROM dbo.table2 AS t
WHERE t.ContactInfo.value('(contactInfo/email/text())[1]', 'VARCHAR(100)') IS NOT NULL;

Before considering performance, just consider how much more complicated the XML-based solution is. Which is easier to understand? Which appears easier to troubleshoot? Which structure seems easier to maintain?

Now look at the execution plans:


... and this is just one record. Imagine trying to join some other tables, adding a GROUP BY or other basic SQL. The queries will be miserably slow and impossible to tune because the execution plan becomes insanely verbose. Just more food for thought.


-- Alan Burstein


Helpful links:

Best practices for getting help on SQLServerCentral -- Jeff Moden
How to Post Performance Problems -- Gail Shaw

Nasty fast set-based string manipulation functions:
For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)
To split strings based on patterns try PatternSplitCM
Need to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8K

I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. -- Itzik Ben-Gan 2001

jonathan.crawford
jonathan.crawford
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4352 Visits: 1286
Jeff Moden - Monday, September 10, 2018 10:34 AM
Couple o' more thoughts. If your demonstrable code for the normalized methodology beats the XML, and I have no doubt that it will, and they play the "Well, can you prove that it will beat the XML in all instances?" card, don't go crazy. Simply state that the normalized method just beat an XML method in at least one case and then ask them if they can prove that the XML method will win in all other cases. BigGrin

Also, don't forget to measure the extra network traffic that the XML method can generate in both directions. I've found that, because of the tag bloat, it's usually in the area of 8 to 16 times more network traffic.

Don't you just have a couple articles you can point them to, Jeff? I'm sure I've seen you do this at least once, maybe in forum posts, I don't know. And again, sad I missed out on you last month, I love "beating them with their own bat".....


-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
Amythyst
Amythyst
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7414 Visits: 820
patrickmcginnis59 10839 - Tuesday, September 11, 2018 12:25 PM
amy26 - Tuesday, September 11, 2018 10:29 AM
Sorry, the argument isn't over storing it as XML ... which was perhaps an explanation error on my part. The argument is just over a single table with a blob field storing every value for a data element using string based values instead of keys etc. They do put some XML into the blob field...but most of the values are just straight characters. It is also pure laziness and no regard for proper design. Especially after having me (for months now) labor to create all the said proper tables and then cause one developer complained "boo hoo I have to change my code", he apparently had enough clout to have it remain as is. I don't really care if the data is stored as XML or not, its just this single table thing that is going to get SUPER huge and some of our data records are one to many relationships... so not quite sure how the hell they plan to handle that.

Are you telling me you spent months designing tables in isolation from your developers? Maybe we should drop the design questions here and you could go into more detail on the process you're burdened with operating within. How did they finish code that they cannot now change, if the tables weren't done? Clearly there should have been some agreement on table structure before either of you started, why didn't that happen?

OMG don't get me started. NO!!! We had meetings and discussions and "understandings". I even have an email from one of the developers a couple days before this last meeting asking me at what point the tables should be populated and getting specifics on things. But then suddenly, they turned the tables (pun intended). A lot of the development they were doing they hard coded things and/or just populating this blob table as a "place holder". Place holder my butt!!! I am also appalled that they aren't following any sort of SDLC methodology... just sort of "get it done" approach. But it will be me that gets thrown under the bus when everything tanks...

Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)SSC Guru (385K reputation)

Group: General Forum Members
Points: 385835 Visits: 42551
amy26 - Wednesday, September 12, 2018 2:45 PM

OMG don't get me started. NO!!! We had meetings and discussions and "understandings". I even have an email from one of the developers a couple days before this last meeting asking me at what point the tables should be populated and getting specifics on things. But then suddenly, they turned the tables (pun intended). A lot of the development they were doing they hard coded things and/or just populating this blob table as a "place holder". Place holder my butt!!! I am also appalled that they aren't following any sort of SDLC methodology... just sort of "get it done" approach. But it will be me that gets thrown under the bus when everything tanks...

Be sure that you have all your CYA documentation. I have seen that turn the bus around on the people trying to throw someone under the bus.


Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
patrickmcginnis59 10839
patrickmcginnis59 10839
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19065 Visits: 7881
amy26 - Wednesday, September 12, 2018 2:45 PM

OMG don't get me started. NO!!! We had meetings and discussions and "understandings". I even have an email from one of the developers a couple days before this last meeting asking me at what point the tables should be populated and getting specifics on things. But then suddenly, they turned the tables (pun intended). A lot of the development they were doing they hard coded things and/or just populating this blob table as a "place holder". Place holder my butt!!! I am also appalled that they aren't following any sort of SDLC methodology... just sort of "get it done" approach. But it will be me that gets thrown under the bus when everything tanks...

I actually am ok with placeholders, its sort of like "stubbing out" code but that's probably not helpful for you in any case. I do it, its a great way to get sections done in such a way to progress a project. Not saying there aren't costs but costs are ok if you can account for them via the benefits. I doubt you're interested in that discussion though, right?

I would be really interested in hearing more from your counterparts about their plans. I don't have that great interest in "sargability" posts and the like that you're seeing here because honestly anybody should be able to read on that, even your counterpart / associates. I'm interested in what your counterparts are doing, but you are sort of coloring the discussion in such a way that I think you'd be better served by just going with the "choir" you are "preaching" to and in any case you aren't obliged to serve my curiosity and that's cool!

I like what your counterpart said to you to be honest, and I'd like to hear more, but I think your posts indicate that you aren't in the mood and this isn't what you're after in this thread so for my part I'll have to let it go at that.

I will say this however, you're implying that you'll get "thrown under the bus" even when documenting your contrary indications to their plans, so can we at least be honest on this one, ie., that it isn't a technical issue? If you think you'll get adversely affected despite your best efforts isn't that really the heart of the issue?

I'd love to play devils advocate and examine your initial "list". In particular I disliked your "ACID" line item and thought it plain that it did not apply. But in explaining my thoughts I'd probably just be typing gratuitiously LOL when in reality I don't think the issue is technical at all and in any case there's plenty of other posters who can make you feel better on this one so there's that.


to properly post on a forum:http://www.sqlservercentral.com/articles/61537/
Amythyst
Amythyst
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7414 Visits: 820
Awe no its all good, I appreciate all feedback. Also, my original post was intended for actual help coming up with legit arguments against the proposed topic. A bit much venting made its way into the thread, so sorry for that. Placeholders are indeed useful and purposeful when their intention is to be placeholders.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search