The topic for this months T-SQL Tuesday #113 hosted by Todd Kleinhans (Blog / Twitter) is about “what do YOU use a database for”. We work all day, night, and weekend sometimes on our database craft. Todd asks:

So what do you use databases for in your personal life that you are willing to share?

Read on to hear my story.

Over the years I have pondered doing different things in my free time to learn more and improve my skills. There are a few things I like to use a database for in my spare time. I will tell you about a few:

• Labs
• Personal Finance / Budgeting Application
• Publically Available Data Sets

Dwelling in the Lab

I love virtual machines. With a contemporary device that has >=8GB of RAM you can spin up VMs on your hardware. I like to use Virtual Box. There are other free options such as VMware Player. There are also proprietary hypervisors you can go with like VMware Workstation.

After I create the VM and install Windows Server I install SQL Server – whichever is the most current build.

There is so much value here to learn. Do you want to play with Availability Groups? How about replication? Maybe Change Data Capture (CDC)? Building your own environment will allow you to take ownership and be vested in your learning. I highly recommend this for anyone looking to progress and hone their skills and understanding of the ecosystem.

Personal Finance / Budget Application

For years I have been off and on with the project of building my own personal finance and budgeting application. The software I tried was all nice but always had something missing. I wish I had more to show here but I’ve burned it down and rebuilt it many times. There is value in that practice.

You don’t really understand how something works until you break it and have to fix it.

Software I Tried

In the past I’ve tried my hand using several different FOSS and Proprietary software for personal finance and budgeting. I’ll name a few:

• iBank – this was for OSX. I think they either got bought or something happened? It was ok but had some shortcomings which caused me to look on.
• Mint – this is the free service by the makers of Turbo Tax. It was easy to use but quirky with connecting to banks. Nowadays I am too much of a privacy advocate to stomach giving all of my financial transactions in exchange for the usage of a web based application which doesn’t fully meet my needs.
• Excel spreadsheet – Yes you read that right. I make a huge spreadsheet with a tab for each month that summarizes all of my income and expenses. This is the most manual of all efforts but more private and contained.

Financial data is something I like to take seriously and protect. That helps motivate each of my failed attempts at making the best personal finance and budgeting application with a SQL Server back end.

Learning All the Components

Another part of the appeal for me is the inclusion of multiple components to make it all work. Example:

• ER Diagram – when you start to plan out how your database will be built you will follow this progression of ER diagrams: start with a conceptual schema –> move to a logical schema –> finish with the physical schema. Do you really want to understand a database? Then study the database diagram.
• Stored Procedures – these will house your business logic. I usually prefer to keep my business logic in the database layer instead of the application layer. You’ll get a lot of development crammed into a small set of requirements. This level of detail is invaluable for troubleshooting.
• SSIS – a cheap and easy way to transform and load data into the schema. Sure you could use BCP, Transact-SQL, the import wizard, and many other options; however, I like SSIS.
• SSRS – a possible presentation layer for reporting.
• Application Programming Language – something like Java or C#. Anything object oriented will be helpful. You can make whatever front end you want and be in total control of the data flow and usage.

Data Dumps and Public Data Sets

Over the years there has been a proliferation of data dumps, data leaks, and publically available data sets. I like to scoop them up and put them in my favorite RDBMS. This can be useful for demos, querying, permanently storing data, and other reasons.

Whatever you do, I encourage you to find the time and energy to play around with the stack of technologies which provide your livlihood. It will pay dividends in the future.

Thanks for reading!

---

If you liked this post then you might also like: T-SQL Tuesday #107 Round Up: Death March Project