TSQL Tuesday #131: Data Analogies

It's #TSQL2sday!!

T-SQL Tuesday is the brainchild of Adam Machanic (Blog | Twitter). December 2009 was the first T-SQL Tuesday invitation that went out by Adam. It is a monthly blog party on the second Tuesday of each month. Currently, Steve Jones (Blog | Twitter) organises the event and maintains a website with all previous posts which you can find here. Everyone is welcome to participate in this monthly blog post.

The Ask

This month's T-SQL Tuesday is hosted by Rob Volk (Blog | Twitter). Rob invites us to write about our favorite analogies that help explain database concepts to people who aren't database experts. Thanks to Rob for hosting this month's blog party!

The School Register

I have been known for using an analogy or two over the years. Some are more well thought out than others! I was going to write about an analogy I once gave about a coffee shop to explain the general principles of why we need to refine requirements, but I was out for a walk with my daughter (she is ten, close enough) this week and with this post in my mind I posed her a question…what does Dad do? With a grin on her face she replied…

You use data to help save the world!

I definitely prefer that to my own explanation :) I am a data consultant, specialising in designing, delivering, optimising data solutions from ingest through to the reporting. Somewhere where she would be familiar with the sort of concepts I work with is at her school. The headmaster needs to collect attendance records during the school year and provides us with a report at the end of the year showing her attendance. So how does that data get to us?

Collect data

She offered that the school register was part of that solution, that each day they mark the attendance of the children in her class. The school register is the initial data collection, because the headmaster doesn’t want to walk around the school every day collecting the information…he could of course, but he wouldn’t get much else done!

That data is collected on a daily basis by every class and that is the starting point of the data collection. Their register consists of a list of pupils down the page and dates across the top and a mark for attendance and a blank space for non-attendance.

Store data

At the end of the month that information will make its way to the “swyddfa” (the Welsh word for office) and be added to the information from other months. This is the next phase of the data collection process, storing an historical account of the information for each child and class.

Present data

Ok, so what if the headmaster wants to assess your attendance for the year? Well he needs to take an average of my data Dad? Yeah that’s pretty good, but it’s a lot of marks to count for just one child. And what if he wants to check every child? Or look at an average for year 5? Or during March? Or who was absent the most during the year? It’s a lot of effort repeatedly counting those marks, and he could lose count so we need to write some calculations that will return this information for him depending on what choices he makes. This next layer of data will provide that information to him and will be able to respond to the questions he asks and return that information really quickly so he doesn’t need to work it out himself.

Enhancing the data

I asked her how the headmaster might look at whether any children didn’t turn up on sunny days. After some pondering, she suggested she could use her phone (groan) to look at which days were sunny and check it against the register. Not a quick process. Adding public data to our attendance data might be useful to find trends, it’s a lot of effort for the headmaster to do this himself, so if we can get that data and match it to the dates on the register, that will save the headmaster a huge amount of time. If he were to pick a name and a month, the calculations would respond by those choices and return attendance information for that choice.

Adding location data

Ok, so what about if we wanted to look at children who are missing school from a certain location. Why on earth would they have our address Dad!?! Well, what about if they wanted to send me a letter about your behaviour? They would just ask you to come to the school and I would have to tell you what I did. Well that’s the end of that then! I did see an opportunity to discuss personal information and the concepts of GDPR did spring to mind for a split second…but I want to her enjoy going for walks with me, so we left it there!