Posts

The three keys pattern for enterprise data design: NoSQL Modeling for Relational Databases

In the beginning, Codd proposed Third Normal Form , and it was good. Today, we need something better... Relational databases are great. They provide a robust and scalable representation of data in a way that people easily understand, and can manipulate with a minimum of training. However, the usefulness of that data is only as valuable as how the data is modeled. In this post, the "three keys pattern" for entity design will be presented. We will attempt to show how it allows relational databases to continue to scale horizontally and vertically for the internet age while retaining the simple query characteristics that are so appealing. Model Complete Entities, not Tables Much of the data we model is hierarchal in nature: parent-child, master-detail, and so forth... When this data is modeled in a database, the levels of the hierarchy are split, and the relationships described. Other data represents the edges between primary entities: uses of, instances of, etc... These

Shredding XML to Tables While Providing Parent-Child Keys

In my last post , I demonstrated how to shape and bend XML from your existing tables in SQL Server. In this post I am going to go the other direction and shred the xml directly into SQL tables with a desired structure. I will also create a parent-child relationship between entities where no keys previously existed in the xml. This approach uses the older OPENXML mechanisms and is distinct from the value() and node() methods introduced later. I believe this approach to be superior and easier to construct than that demonstrated by Adam Machanic . I'm assuming here that you've loaded the xml into the @xmlDocument variable (as in my previous example) You need a document handle (@XmlDocumentHandle). As long as this handle remains open, the id's returned for each node do not change. Use the OPENXML function with the @XmlDocumentHandle and create an XPath query that finds the data for the you want. The value of 9 means to map attributes to your defined table, and handles r

Building nice XML from SQL Server Tables

The XML functionality of SQL Server is vast, however, the best subset of that functionality that I've found may be demonstrated in the following example: Unfortunately, the example result is listed before the code. Join all the desired tables together Set the desired root element Set the path to an empty string to avoid an extra XML element at the root of each resulting row Provide an XPATH name with attributes for each of the desired columns (select * doesn't work well) Keep all the selected columns together by XPATH name or you'll split your xml attributes See my next blog entry for my best way to shred the xml

String_Split function for Pre-2016 SQL Server Databases

SQL Server 2016 has (finally) a built-in string split function: STRING_SPLIT ( string , separator ) That is great, however, if you are using an earlier version, many people end up writing something that loops through the string and wherever the separator shows up, the substring is copied to a table, which is returned later. Here's an alternative... replace the separator with XML element tags and then use the built-in XML functionality to return the delimited elements. In theory, this may be slower than a tight string loop, but in practice, the XML libraries are extremely efficient, and the REPLACE function is also highly optimized. It certainly takes less code than the other approach.

Database Projects, SQL Unit Tests, and TeamCity

Image
Continuous Integration  is a really good idea, especially when you are working with a team of developers. There are lots of tools out there that can help your team build better quality into your application. TeamCity by JetBrains is one such tool that has been around for awhile; specifically, it is the one that my company uses. Visual Studio has Database Projects   SQL Server Data Tools  to address some of the inconsistencies that developers face when using a database as a storage layer; Things like version control and consistent deployment. It also includes SQL unit tests , which fills a huge gap if any  of your logic is in the database. Many organizations moved away from logic in the database years ago because it was tricky (at best) to verify logic on the data tier, of course, they were also making changes directly to the database itself... but I digress. If you are using SQL Server Data Tools, and run your unit tests locally, it is a joy to behold. Your database is automatical

Technical KnockOut

The other day I was working on a little demonstration of using OWIN to serve local files, use dependency injection, and server Web API in a modern SPA (Single Page Application). Almost as an afterthought, I decided to use KnockoutJS and jQuery from a minimal HTML page. This was overheard by colleagues, and a firestorm erupted... "You should have used AngularJS instead; It would take less lines of code." So I pulled down AngularJS and changed my demo to placate the angry threats wishes of the religious zealots thoughtful developers at my desk. For the record, this is the code I wrote using KnockoutJS and jQuery: and here is the AngularJS version: Trying to provide equally readable formatting, the Knockout solution takes one less line of code even though you are loading two JavaScript libraries. The only fair way to count lines is to count semicolons. Both have three. That's a tie. What about the size of the libraries being loaded. Well, Angular includes

Not everyone needs to be a software developer...

...but everyone should learn to code. I have friends who are pharmacists, lawyers, police, firemen, military, mechanics, and musicians who also write code to help them out in their jobs. Software is a tool, a versatile and powerful tool, that can be applied to any occupation or industry. Learning to code is a 21st century skill that should be required in every middle school and high school in the country. I'm not alone in this belief. There are lots and lots and lots of organizations dedicated to helping people learn to code. Even the Prime Minister of Singapore posted a Sudoku Solver that he wrote while an electrical engineering student as encouragement and an example to the world that we need more people who can code. I think that code may have been more approachable when I was learning. The video games we had were blocky and you could understand how they could worked with a minimal amount of instruction or none at all; how many versions of breakout and lunar lander mu