Posted in SQL Server, The Survey Project

What’s your address?

Now that we have a Person table to answer the first half of the first question on the questionnaire, it’s time to look at the second half: what is your current post office address.

Again, it’s something that seems something fairly simple. So let’s start with the basic of what the data we’re trying to find. For me, this means we need to create address or location tables. Addresses are fairly common things to model, which means there are a lot of ways you can do this. This also means that it’s easy to overthink or even over-design. So to help prevent that, let’s define what are our requirements for these tables.

  1. We’ll need to store the state.
  2. We’ll need to capture either the county or city.
  3. I don’t know if we will get street information.
  4. We won’t get zip codes since those weren’t introduced until the 1960s. (Fun Fact!)
  5. We may need to store countries if any addresses in our survey are outside of the US.
  6. Taking a look through all of the questions, we may have multiple types of address information for a person throughout the survey. For example, this is the current location but we may have a location where they enlisted, were born, grew up, etc.
  7. After browsing the questionnaires, we may need to capture changes to locations as part of future questions. For example, West Virginia broke from Virginia and became its own state during the Civil War. One of the survey examples notes that the county name changed.

So how do we design our tables to reflect these requirements?

Most of the requirements listed above go together. To include the items except #7, I’ve come up with this:


I’m not calling it an Address table but a Location table because I think there are other cases for this table where using location may be more appropriate. Note that for locations, I’m making the alternate key the person and the location type. In many situations, this may not work because there could be several location types for each person. Right now, I’m going to start with the criteria that there’s only going to be one location for each type. As this is just a model right now, I can change it later as we take a closer look at other questions.

The last piece for this is how to handle changes for things such as county names or city locations. Again this is another place where there are a lot of options. In figuring out which way to go, I decided I wanted to go with a simple solution that focused on the information that I cared about. I don’t care about tracking that West Virginia was once part of Virginia but I do care about knowing that the Wheeling, Virginia before the war is now Wheeling, West Virginia and I do care that parts of Grainger County, Tennessee became Hamblen County.

02 - Locations with history.jpg

I thought about just using the Location table as a way to keep track of city\state or county\state changes but we could also be dealing with cities or counties that are renamed. I think this would be a cleaner way to create that connection.

With that, the first question is modeled. So to double check that I’m on the right track, can I look at this model and see if it can hold the data to answer the question:


I think the answer right now is yes.

[If you want to see the thought process behind modeling first part of this question (State your full name), check out the post here.]


Posted in SQL Server

Temporal Tables Take 2

For the T-SQL Tuesday challenge in June, I took a look at temporal tables. When I was querying the table to show the changes, I found that when I selected from the temporal table to return the history records as well, not all of the rows I expected to see were being returned. My theory was that it may be a timing issue where a date time precision that prevented all of the updates from being written.

As I went through this second look, I realized that my original theory was off. I know it’s not the first time this will happen and it’s not going to be the last. But this is why we blog – to learn from our mistakes, right?

Continue reading “Temporal Tables Take 2”

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 SQL Server

Adventures in Installation

So I got my new laptop – 16GB RAM, 512 SSD drive, i7-6700HQ Quad Core processor. There’s no DVD drive, so I may need to get an external one at some point. It’s probably a little more than what I need but as I tend to run tech into the ground before replacing\upgrading, I’m OK with these specs. My “perfectly fine” laptop has also starting to mock me with strange display driver issues, so I’m definitely feeling better about making the plunge. I even got myself a new wifi router since I suddenly realized that I had way too many connected devices and I may benefit by upgrading that as well.

With my shiny new laptop, the first thing I had to do is install SQL 2016. I don’t do much SQL Server installations these days. If someone I work with needs help installing SQL Server, I’ll be there to help answer questions but I don’t do the installs for them. And because I really only need the one instance of each version locally and (clearly) don’t change machines that often, it’s been awhile.

The installer, for the most part, still looked and felt like the previous installs. But there are a lot of different details.

Continue reading “Adventures in Installation”

Posted in The Survey Project

Extra Credit Reading List

We’re coming up on a long weekend here in the US. The new laptop just arrived (Yay!!!!) so I’m looking forward to really setting it up and jump right in. (Guess what my next post will be about?)

So I think we’re ready to start jumping in. If you’re interested, you can start reading through the survey questions we’ll be modeling. You can find them here: There should also be some samples of the answers here as well if you wanted to see what some of the answers are.

I have also decided to give myself “extra credit reading assignments.” If I’m going to do something based on history, I should brush up my knowledge on the subject. I’m not trying to be an expert or anything like that, but some of the details may come in handy.


Continue reading “Extra Credit Reading List”

Posted in T-SQL Tuesday

T-SQL Tuesday – June 2016: Temporal Tables

It’s T-SQL Tuesday. This month, the assignment is to write about SQL Server 2016. (A big thanks to Michael Swart (b|t) for hosting!) I have decided that I really do want to use SQL Server 2016 over SQL Server 2014 for my longer project. While I don’t have that set up just yet (more about that another time), I decided to test out the Virtual Labs with SQL 2016 for this.

One of the new features I’m excited to look into is the Temporal Table. It’s a table that where SQL Server creates a history of the records changes (inserts, updates and deletes) but will only display the “current” version by default, based on the criteria set up as designated upon creation. Here’s the more info about it:

I’ve worked with a project where we have separate activity tables to keep track of all types of changes. The application and any changes we’ve had to do in scripts for deployment had to define and execute that code separately. So I can see where something like this would be useful.

Continue reading “T-SQL Tuesday – June 2016: Temporal Tables”