Posted in data modeling, SQL Server, The Survey Project

Rethinking & Redirecting

classic blunder memeI feel like I made the classic blunder. Well, one of the classic blunders at least. The good news is that I didn’t get involved in a land war in Asia or go against a Sicilian when death was on the line. But I did fall for one of the even lesser lesser known ones – I didn’t practice what I preach.

If I think about the main theme I’ve been talking about lately, it would be don’t just go through the motions of doing things but really think about why you’re doing what you’re doing. There are so many places where this applies in life and I really do think it’s important.

I feel like I’ve fallen down on that idea when it comes to this blog. Let me try to explain:

When I started, I gave myself a project based on a research paper I did in college. But I haven’t worked on it for a while now. It’s taken me some time but I think I figured out some of the road blocks I’ve been struggling with for a while. And I mean some of the problems that I was facing before I allowed the excuses of “I’ve been busy” or “I’m focusing more on speaking these days” to get in the way.

When I gave myself this project, I thought it would be good practice for building a database because I saw the potential of how it could be use. I wanted to use a database as a way to see the connections between the people who returned the survey: where they were from, what unit they served in, where they fought, who they served with, etc. The way I approached this was by taking each question, breaking it down, then building on what I came up with using the next question.

The biggest problem with this approach was that the questions aren’t written in a way to get standard answers. How do you extract the data for a relational database structure? Non-standard answers make it very hard to interpret the data points.

Another problem with the questions is that they were designed for the data to lean a certain way or left out information that a modern viewpoint needs. Unfortunately, some of that bias – especially in today’s world – makes it even harder. Some of the implications of these questions – even just having this as a subject in some cases – are beyond what I have the means to handle properly and to be honest, I’m still struggling a little about what to do with that.

By just using the questions alone dictate the database design allowed me to lose sight of what I was hoping to gain by doing this. I stopped focusing on why I was creating the database and I lost the direction I wanted to go with the project.

If I were to start over, I think I would take a different approach. Some ideas on how I might do this are:

  • I would clearly define the goals of the database:
    • As a historian, I want to see the statistics:
      • how many from the same regiment
      • how many from the same town
      • where did people enlist
    • As a genealogist, I want to be able to research family members –
      • find a particular person and find parent and grandparent information
      • find related members
      • be able to add known associations from outside sources or link these records to those 3rd party sources
  • I would divide the questions in a way to see which ones answer or partially fit into each of the goals listed above.
  • I would allow myself to modify questions in such a way that I could standardize the answers. Obviously, I can’t standardize the answers I have. But let’s face it – I’m taking something that was never designed for something like a standardized data model and forcing it into that. I have to make allowances. I’m not populating the data but creating logical models. If I had more time and more incentives, this may even be a case where using something like NoSQL databases could help create the standardization I don’t have. Plus AI may have option for interpreting the written responses and help interpret that and create data that would give us data for subjects such as literacy rates. But that’s much farther down the road than where I am; I’m just setting up the basic structure.
  • I would research other data sources where similar things have already been done to see if I can understand how those models have been set up. After one of my sessions where I mentioned this project, one of the attendees sent me a link to a similar project. Reading through that may give me some of the additional knowledge that I don’t have that can help me with this.

One of the reasons I started this project was so I could get better at database design. While I’m not working on this project directly, I have been doing sessions on database design, using first lines of a books or baseball as my examples. And I’ve enjoyed working on those.

However, I have a history of starting projects and not following through sometimes.(Would you like to see my “collection of craft supplies” or my poor neglected mandolin?) I’m not ready for this project to fall in that category. Even if I’m not working on it regularly, it is in the back of my mind and I am reminding myself to think about how I can make it better. It’s inspired me to do some of the sessions I’ve given. Maybe this just falls under one of those “Things don’t always go the way you expect them to.” But I’m not quite ready to give up but I’m not quite ready to start from scratch. It may take me a while, but I want to spend more time to figure it out what I want to do and where to take this. It’s hard work to do by yourself. Luckily, this project was for when I couldn’t find other projects to do or blog about and I definitely have found a lot of those.

So stay tuned… I’m using y’all to keep me accountable.

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 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”