Posted in data modeling, SQL Server

How I Really Feel about Surrogate Primary Keys

When someone goes on a rant about something they truly feel strongly about – good or bad, my typical joking response is “You’re being a little wishy-washy about this. Tell me how you really feel.” I recently had a conversation with co-workers about surrogate primary keys and realized I need to use that line on myself.

So let me tell you how I really feel about surrogate primary keys. Where’s my soapbox?


Continue reading “How I Really Feel about Surrogate Primary Keys”

Posted in data modeling, SQL Server

Designing Super Types of Tables

I want to talk a little about database design patterns. When working with a relational database, there are a couple of patterns that exist to help you normalize your data. I think one of the most useful patterns in this is the supertype-subtype relationship.

You don’t see a supertype-subtype relationship defined as such when you’re looking at the physical database. You’ll only see it explicitly in the logical data model. So what is the pattern and how do you know that you have one in your database?

Continue reading “Designing Super Types of Tables”

Posted in SQL Server, The Survey Project

All in the Family

allinthefamilyI wanted to spend some time looking at questions about people to see if we can fill out some of the Person table, or at least confirm it makes sense. So I’m going to start skipping around the survey looking at different questions to try to find more information for people.

The next group of questions about people have to do with parents and other relatives.

Continue reading “All in the Family”

Posted in SQL Server, The Survey Project

How Old Are You Now?

Time to look at Question #2 of the survey:


Here we just have an age with no additional context. So it makes it a little more interesting to try to figure out what we want to do. This is why it’s so important when defining surveys these days to make sure you’re clear about how you want the answers to look. And it’s easy to do that when you have a good idea of how you plan to use that data. Unfortunately, we can’t revise this survey so we’ll go with what we have.

Continue reading “How Old Are You Now?”

Posted in The Survey Project

Ready, Set, Model!

We’re here!!! We’ve finally come to the part where we can start modeling and creating our database. As I was creating a clean reference between the two versions of the survey questions for myself, I realized that there are really two types of questions – ones that were fact based (where were you born, what was your occupation, etc.) vs. the ones that were more observational based (i.e. what was your sense of X scenario). So we’ll start with the fact based questions. Continue reading “Ready, Set, Model!”

Posted in Misc

Time to Set up the Computer

Before I can jump in and start building the database, I need to set up my computer to get this to work. Technically I could probably use my work computer for a lot of this but that could be kind of awkward for so many reasons. I think I’d feel more comfortable on my own personal machine.

So there are a couple of things that I need to set up to get going on this project:

Continue reading “Time to Set up the Computer”