I 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.
So, what are the questions about the family?
I see a lot of different functional areas packed into these questions.
- People
- Relationships
- Locations (parent’s home, parent’s place of birth, family country of origin)
- Jobs
- Military service
Let’s start with by looking at what I’ve already started modeling: People and Locations. For these two subject areas, will I be able to hold the data for those areas from these questions into those tables or are there adjustments needed?
Person
I think the Person table should be able to hold the basic information. One of the things I was hoping to figure out by looking at some of the extra fields and figure out if there were too many NULLs on that table in terms of what I was looking at. If I leave those fields as they are modeled now, I think the Person table should still be able to hold this information as it is.
The one thing I think I will add to the Person table is the ability to store gender. I am going to create a gender type table to go with it. I recognize that I’m likely only going to have at least 3 gender types – Male, Female, Unknown. If I were doing this for today’s world, there may be more options that need to be tracked so having reference table allows expansion for the way we need to store this info. Overall, a gender type table will allow any sort of modification or customization needed without much change. It’s a small lookup table but I’m going to put it in there.
Location
The second area of functionality that I’ve already started to look at is the location. There are a lot of different locations listed here. We’re allowing for a lot of different location types so I think there’s a lot of flexibility already in the tables to hold these different types of address. This is where having a location type table can help with the design.
I think what I have right now works from a logical standpoint for locations. As we look at more locations, we’ll be able to double check that some more.
Relationships
Now we need to introduce the ability to create relationships between different people. Connecting the people in these surveys to each other is one of the reasons I decided to use these surveys as a subject.
As I think about the types of relationships I need to store, I have to be able to handle parents and other relatives. Spoiler alert – there are other questions that ask about other people that the veteran served with so we’ll need to store those type of relationships as well.
I’ve done a bit of other relationship type modeling in the past and I’ve spent most of it looking at Len Silverston’s relationship models. (I have a copy of his The Data Model Resource Book, Vol. 1.) He goes into a lot more depth into the various types of relationships and details about them but I am not really going to be able to use all of that. I will take those ideas and narrow it down to the essential information that we would need here. For our needs, we really just need to say that Person A has this particular relationship type to Person B. Based on this sentence structure, I would say Person A is our “From Person” and Person B is our “To Person”.
The trick to this table is deciding on who is Person A and Person B. The relationship type names would determine this. If your relationship types are “Father”, “Mother”, “Maternal Grandfather”, “Maternal Grandmother”, the Person A would be the parent or grandparent and Person B would be the child or grandchild. If the relationship was “Grandchild”, “Son”, “Daughter”, then Person A would be the child or grandchild and Person B would be the parent or grandparent. You may also decide that you want to store both versions of the relationship and that would be OK too.
The other thing to consider is whether two people can have multiple relationship types. For example, a person could serve in the same regiment with his brother. We want to make sure this table could hold this information.
Based on this, here’s what I’ve come up with to allow for the changes I’ve made here for relationships and gender:
I didn’t really solve the NULL issue with the Person table, which was part of my original goal but I think that’s still OK for now.
What do you think?
Deb the DBA 