Michael J. Swart

July 26, 2012

Data Modelling: Composite Relationships

Filed under: SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 12:00 pm

Takeaway: In this article, I recommend that you use composite primary keys for child tables in composite relationships. If a child record is part of its parent record, then the parent’s primary key should be part of its child’s primary key.

That means that this:

create table CD
    CDId int identity,
    Name nvarchar(max),
    Artist nvarchar(max),
    Genre nvarchar(max),
    primary key (CDId)
create table Track
    CDId int references CD(CDId),
    TrackId int,
    Name nvarchar(max),
    primary key (CDId, TrackId)

… is better than this:

create table CD
    CDId int identity,
    Name nvarchar(max),
    Artist nvarchar(max),
    Genre nvarchar(max),
    primary key (CDId)
create table Track
    TrackId int identity,
    CDId int references CD(CDId),
    TrackNumber int,
    Name nvarchar(max),
    primary key (TrackId)

A CD of Milli Vanilli

First some definitions, and then I’ll briefly describe the benefits of this practice.

Composite Relationship

I’m using the UML definitions when I talk about these relationships. Composite relationships between two tables occur when a child points to a parent and when the child is considered part of the parent. A quick test for this relationship is to ask whether records are deleted when the parent is. Or you can ask whether an object gets copied when the parent does. Track-CD is a good example of an composite relationship. Other examples might be Department-Company or SalesOrderDetail-SalesOrderHeader .

Relationships That Are Not Composite

For contrast, some relationships that are not composite include aggregationassociation or dependency relationships. For example, the driver-vehicle relationship is not an composite relationship. It’s a “has a” relationship. Both vehicles and drivers have their own life cycle and it’s common for vehicles to change owners.


Since the child records live and die (and get copied) with the parent record, it makes sense to have the parent PK column be the first column of a child’s primary key. The values of the child’s primary key almost never change. Some other benefits:

  • Index lookups and index scans become more efficient.
  • When you extend this practice to children of children, it’s even better. You have the opportunity for less joins on many queries.
  • The SQL you have to write turns out so much cleaner. I hinted about that here: My Favourite Data Modelling Example.
  • In some cases business rules can be enforced that couldn’t before.
  • Delete statements and purge strategies become simpler.

Is This Obvious?

Maybe when you read the Track-CD example above, it was obvious to you that the first example is better. But I see the second example way too often. So often that I felt it deserved a blog post.

If you want, you can look for this stuff in your own tables to double check. Try this out a couple times:

  • Find a foreign key somewhere in your database.
  • Is the relationship enforced by the foreign key an composite relationship? In other words, is one record part of the record it refers to? (e.g. Tracks are a part of their CDs)
  • Then are the foreign key columns part of the primary key? I hope so.

July 17, 2012

T-SQL Tuesday #32 – A Day in the Life

Filed under: SQLServerPedia Syndication,Technical Articles — Tags: , — Michael J. Swart @ 8:00 am

T-SQL Tuesday Logo

So it’s T-SQL Tuesday time again! This month it’s hosted by Erin Stellato. The topic this month is interesting. It’s got a bit of a twist. Participating bloggers are asked to keep a diary of sorts for what they did last Wednesday July, 11th 2011. I came into work last Wednesday feeling a slightly more interested in the day ahead. The day seemed a little more significant than other ordinary days. Would it be good, bad or average? It reminded me of a movie (Groundhog Day) about another day with a lot of scrutiny:

Anyway, it’s always interesting to compare someone’s job title with their actual activities and I can’t wait to read Erin’s round up. Here we go:

Michael J. Swart: Senior Database Developer


Some normal morning Activities.  I took a new way into work this morning. I came in at 8:00 and got a large regular coffee from Tim Horton’s. The first thing I did after logging into my computer is open up OneNote for taking notes today. And now I start answering email:

  • Our company announced support for our product on SQL Server 2012 recently. I answered a few questions about what that means for developers exactly.
  • Looked at db statistics from the previous day. Decided to follow up on an un-patched database that was hogging the resources on its server. There was a bit of stress here. I have responsibility for something that I don’t have enough control over and I had to sort that out.
  • I launched some tests earlier this week. These tests exercise a utility I wrote that deletes data. Because the tests last a few days, I checked up on their progress to see whether they’re still running (They are).
  • Stand up time! (Scrum) Where I learn what others on my team are doing. 9:30

Coffee #2

  • Had a conversation with colleague about implementing a locking mechanism (similar to mutexes) at the database level.
  • Tuning a two line query (which used a view in a view in a view) which is more like an 80 line query. Did I tell you I dislike views? They mask complexity and stuff like this happens. Just now I wrote a quick and dirty program to help me expand view definitions inline. I couldn’t quickly find a free utility online. It’s a cool simple program that I would share if I hadn’t written it during work hours.
  • Woohoo! My first interruption of the day. There’s a particular third party app we use that doesn’t have a client timeout setting. Long running queries cause impatient users to hit refresh and now there’s two long running queries, then three, then four… With the help of sp_whoiscactive, we went from panic to understanding everything relevant. And the brought the server from unresponsive to responsive. The whole process lasted less than two minutes (Thanks again to Adam Machanic and his sp_whoisactive sproc!). Now I have to spend some extra time following up on this. I’ll probably take the next 15 minutes working with the issue tracking software we use to log this incident and make it possible for follow up activities.
  • Interruption 2! A mercifully short question: No you can’t restore a 2008 R2 backup onto a 2008 server.

Lunch time

Went to a used book to pick up Drawing on the Right Side of the Brain. My blog readers voted for Developing on the Right Side of the Brain to be my next post (published last week here) and I don’t have a well-thought-out idea for what the post should be about. The book should evoke some ideas. We’ll see how it goes.


  • More time looking at the poor performing queries I mentioned earlier. Finally got to the bottom of it but with no easy fixes, it was time to discuss other options. I had a meeting about that. Some hard choices here. Not fun. Let’s move on.

3:00 Coffee #3

  • Started on a new project just now. I have to create a utility that takes a database and makes everything anonymous (no personally identifiable information). Easier said than done I think!
  • Woohoo! Another interruption. And another interesting discussion about shrinking databases!

5:00 Home Time

Pasting this OneNote doc into wordpress to be edited later. Overall this was an average day I think.

Some More Thoughts

All this goes to say that there’s a lot of variety in this day in my job. And overall, I think that this day was pretty typical for myself. There was some overlap into DBA territory (production issues) and there was some overlap into Business Intelligence territory too. About 60% of my day was spent on activities I had planned in advance which and 40% then on surprises.

I’m really keen to see what the rest of the SQL Server bloggers did this day! Thanks Erin for hosting and the great topic.

July 12, 2012

Developing on the Right Side of the Brain

Filed under: SQLServerPedia Syndication — Tags: — Michael J. Swart @ 12:00 pm

Last week you voted for “Developing on the Right Side of the Brain” as the post you wanted to see most. Here we are. Enjoy.

When learning to draw, I took a big step forward when I worked through the exercises in a book called Drawing on the Right Side of the Brain by Betty Edwards. I thought the premise was cool. She explains that we have to use our intuitive right side of the brain instead of the usual analytical left side in order to access untapped drawing skills.

But it’s pop-psychology. The premise probably makes neuro-scientists cringe (just like the following myth):

It’s possible that her whole book is like Dumbo’s feather. Something to hold onto that helps you believe you can draw. (And I can’t fault her for doing that. There are only two types of people: those who can draw, and those who just don’t believe they can yet.) But her book is more than just a placebo; her method works very well.

While going through her book again recently, I thought of some similarities between the world of drawing and art and the database world.

Fresh Points of View

One of the early drawing exercises the author has us complete is to copy a line drawing. But the drawing we’re to copy from is upside down. It’s counter intuitive but these upside-down drawings often turn out better than if the drawing was drawn normally.

The theory is that when drawing from a picture up-side down, it is less likely for students to use old drawing habits. So for example, instead of drawing a nose, the student will draw what they see. Probably because they have no experience drawing an upside down nose.

In the database world, I’d like to see new developers drop a few bad habits from the beginning. Two that come to mind:

  1. Tables are just Spreadsheets: These newfangled database tables seem almost exactly like the Excel spreadsheets that I’m more comfortable with. So if i’m storing my employee’s schedules in my database, it’s natural to define columns called Monday, Tuesday, Wednesday, Thursday and Friday because that’s the way I want it displayed.
    One thing that might be a bit more subtle: It’s not too hard to convince people to define normalized tables. It’s a little harder to convince people not to pivot in the database either. In other words, don’t figure out how to write a query that returns columns Monday, Tuesday, Wednesday, Thursday and Friday. Figure that out in the application.
  2. T-SQL is just Programming: Okay, so I need to process this data. It shouldn’t be too much harder than C#. Oh look, there’s an IF statement and WHILE statement. Now all I need to know is how to access each element of a set. Lets see what this CURSOR statement does…
    CURSORS have their place, but it’s not a particularly nice place. I’ve used them in the past but only in special circumstances. I see cursors written by others too and most of the time they’re not needed. Over 90% of the queries I write begin with INSERT, UPDATE, DELETE or SELECT

Practice Makes Perfect and Interest Makes Practice

For me the exercises in Drawing on the Right Side of the Brain helped me improve rapidly. The speed I improved was surprising. From zero to mediocre didn’t take me a month. It took me a week. From mediocre to good didn’t take me a year, it took me maybe a month. This fed my interest. The interest led to more practice. More practice led to even better results. It was a beautiful cycle and everything a hobby or passion should be.

Drawing sketches led to pastel paintings, life drawing classes and to digital paintings (not necessarily in that order). And I’m still learning and have a long way to go. (For example, Toronto artist Rosemary Travale drew this picture of Captain Picard and wish it were me. I want to be able to draw like that.).

So practice makes perfect and the database field is no exception. Once the basics sink in, it makes the next things easier.  There’s always something new to learn. Hopefully the field is as interesting for you as it is to me. For me it makes the work a “chosen profession” instead of a job.

And You?

What’s your hobby? Piano? Chess? Frisbee Golf? Recreationally writing articles for mathematics journals?

July 4, 2012

Go Democracy!

Filed under: SQLServerPedia Syndication — Michael J. Swart @ 1:54 pm

I’ve been having a bit of a writers block but just today I thought of a number of ideas that I’d like to write about. So in honour of American Independence Day, I thought I’d be all democratic about it. I want you to vote for the next blog post I write and publish:

[Update July 5th: The polls are closed. Thanks for voting everyone] 

The choices were:

  • Keep It Simple SQL Server, 28%
  • Programming on the Right Side of the Brain, 38%
  • Why Did SQL Server Pick That Plan, 34%


So expect a post about the right side of the brain soon (stay tuned!)

(By the way, I think you Americans should have stuck with us. Then we’d all be Canadian!)

Powered by WordPress