Data before year's end

I'm going to apologize right at the start- this is not a Christmas blog, nor is it a year-in-review blog. It's merely a near end-of-year blog about the state of my data.

So, what, exactly, does the author wish to communicate? Well, for starters, I ran into an interesting enigma in my data....

Long ago, I purchased some CD's that had sound effects. Today, the internet has made them fairly irrelevant. So, the not-so-big data question is this: do I include them in in my inventory, even though I may never use them? Honestly, even though this database IS at least in part intended for insurance purposes, I don't think I'll miss those two disks. How about videos? I have a DVD produced by the Chicago Tribune which commemorates 9/11. It was free with my newspaper subscription, and is probably irreplaceable. But- I'm guessing it's hardly collectible. So, that one will also NOT make the cut. Books- both physical and e-books... I'm nowhere NEAR starting on those, but I think you can see the trend.

Owning the data gives the DBA a huge advantage, as is shown in the previous paragraph. The problem, though, is that if you're a small-time operator (as I am), there's no one to fact-check, proofread or cover your (data) back. So, I'm hoping to correct at least one of these deficiencies in 2019, by enlisting someone else to do some of the data entry (we'll see how that goes!).

*****Happy New Year*****

Well, I want to publish this, so I may as well post a few New Year's thoughts. For starters, it's officially 2019 in my neck of the woods, so Happy New Year. I'm enjoying what is probably the best egg nog I've ever had (from Southern Comfort- but no alcohol in it) in a vintage 1960's vintage tumbler. The tumbler is tall and narrow, with dusty rose screening of different cities around the world- rather appropriate for New Year's Day, n'est-ce pas?

I don't follow many other blogs, but I do follow some blogging "experts" on twitter. I generally agree with the "experts"-  one of the secrets to blogging is frequency; the other is content. If you've been following this blog for any length of  time, firstly, thank you. And secondly, thank you for putting up with my subject matter- I'm hoping that this is a reflection on YOUR eclectic reading tastes.

And FINALLY, the state of the database, December 31. 2018. As of the last entries, the music database stands thusly:

Number of groups: 156

Number of albums: 79

Number of songs: 1035

And that is all for 2018. I wish every reader a peaceful, joyous and profitable 2019.

As always, I am hochspeyer, blogging data management and and analysis so you don't have to.

This IS normal(ized) in my world

I've mentioned once or twice (I think) that I'm not a professional DBA; I do this stuff for fun! (??? ORLY?)

Yup, 'tis true: it's something of a hobby, or repast, or possibly a fairly intense interest. In any event, this interest has made me money before, and hopefully will do so again. This particular post is especially ironic from a few angles: for starters, I've also mentioned that I try to do as much normalization of a database right from the start, and if you read the last post AND/OR you're a dba or involved with databases, you'll note that I've pretty much thrown out all of the data normalization conventions for this flat database. The reason is pretty simple: I know my client (me!), and I know what data WILL be needed, and what data MIGHT be needed. Also, in the end, this Excel workbook is still data.

So, to recap, I have a .txt file that I have converted into something resembling a flat database. Three of the current four columns have repetitive (if not identical) data. Why?

The answer is simple in my context, but if this were someone trying to apply a fix to an existing problem for, say, a paying client, and trying to explain that you had to create repetitive data to make the resulting database entries more efficient... well, not necessarily so simple.

I was talking with one of my coworkers earlier this week, who is a data guy at heart. A month or so ago, he had taken a stab at trying to extract those target part numbers from the text file and had dissatisfying results. The other day, I showed him the new and improved .xlsx data file, and warned him that this was something akin to anti-normalization. When he looked at the file, he cringed in agreement. However, once I explained exactly why I had created columns with duplicate or near duplicate data, he agreed with my process. And therein lies the proverbial "rub": even though I've formatted this .xlsx file as a flat database (which came to me as a .txt file, which I'm guessing was extracted from some other format), it isn't a database- even a flat one.

It's a dataset. Period. And as it is a dataset, it needs cleansing, not normalizing.

Now, having said that, the casual reader is probably wondering how this dataset will be utilized. Well, THIS is where it gets interesting (ORLY??). Before I can import ANY data into the database, it needs to be usable. In my world, this means I need to be able to count pieces by size, color, family and type; in other words, I need to make this usable by Forty-Two (the master database). So, once I clean up the data, my goal is to import all of it into Access as a part of the inventory.

That day is still far away.

As always, I am hochspeyer, blogging data analysis and management so you don't have to.

A database against the rules

In my previous blog I noted that I had restarted work on my database. In this post I'd like to talk a bit about database design and construction.

I'm guessing that most of my readers who are interested in data will fall into one of a very small number of categories. The first is a professional DBA who is either interested in seeing how other DBA's do things, or possibly here just for a laugh. The next might be someone tasked with throwing together an ad hoc database with a good amount of existing data, and presenting it in an easily digestible format without creating all sorts of dashboards,relationships, tables, etc. I'm certain there are many other scenarios which may pique one's curiosity, but my aim in documenting my journey is to stimulate an appreciation for and a desire for the application of data.

*For database neophytes: please tread carefully. A long time ago, I read something someone wrote that stuck with me: they said that a great database design starts on paper. So, unless you're like me and can "see" what your database will look like (and/or have built similar databases), design your database first, then start coding. 

Every database has a raison d'être. It's highly unlikely that anyone would wake up one day and say, "Hey, a database of all of the dry pasta in the house would be useful!" Obviously not... I think! (For the record, if you ABSOLUTELY need something to keep track of your pasta, a barcode scanner (or smartphone scanner app) and a spreadsheet would probably be a better solution.

When I was in high school, my appetite for data was whetted by my love of music. Personal computers were just starting to come on the scene, and I didn't even know what a spreadsheet was. Everything I did was done in ink on looseleaf paper. After a while, I graduated from just cataloging my music to staging popularity polls with friends and acquaintances. Quite surprisingly, most of them objected to my methodology, which was quite simple, and leveled the playing field. It worked like this:

A person could vote for up to ten songs and ten albums.I made sure to tell them to rank their choices by most favorite, then next most favorite, etc., so that the top song on a ballot would receive 10 points, the next 9, etc. My reasoning was this: just because 10 voters like Hey Jude, not all like it equally, and the vote should be weighted accordingly. 

For some reason, no one liked that idea.

But that, for me, was the start of databases. Stay tuned!

As always, I am hochspeyer, blogging data analysis and management so you don't have to. 

Tables and chairs

In my previous post, The Elusive Balance, I had mentioned that my goal was to enter twenty-three CDs into the Media_Title table. I am happy to report that this goal was reached, and the rack they were destined to fill is now completely occupied. After doing the updates to this table, however, I discovered some flaws in my "main" media table, which has caused me to reconsider the design of my database.

I wish I could remember where I had read this, but someone once wrote that the best way to start a database is to design it with pencil and paper. Although I've always agreed in principle that this was a great idea, in all of the databases which I've designed, I've rarely heeded this sage advice. This is partly because planning things often results in frustration and headaches for me, but also partly because I can "see" with my mind's eye what the database will look like and what it will do prior to a single keystroke being executed (note to the faint of heart and the true database newbs: semi-professional DBA on a closed course. Do not attempt at home or work. Especially at work).

One other caveat is necessary when discussing database design: don't be too afraid of change.

When the idea of Forty-Two first came into my mind is hard to say- it predates this blog by a few years at the very least. The concept was fairly modest, at first, and didn't even actually have music or media as its primary focus. It was actually my Lego collection. This was another project that grew by fits, false starts, and occasional bursts of inspiration and diligence. And it was done entirely in Excel. Over time, though, I saw the possibility of building something more powerful (and consequently more useful) in Access.

The earliest Access versions- starting in Access 2003, then moving to 2007 and finally 2010- were not grand by any stretch of the imagination, and were at first only intended to catalog music CDs. Eventually, movies seemed to be a natural addition and were incorporated, followed by books, software, console games and finally books in the latest iteration. And all of it goes back to Lego.

One of the problems I have with Lego elements is that although I do not have a large collection by many AFOL (Adult Fan of Lego) collectors' standards, I have enough to necessitate them being stored in different containers and in different locations in our home. As I was working in Excel, I found that I didn't have a really good way of taking this into account. When I realized that Access could handle this particular problem, another thought occurred to me: I could do this with media as well, and eventually incorporate things that were totally unrelated, and this could be useful in insurance planning.

That's the short version of the history of Forty-Two. The immediate future, as alluded to in the first paragraph, will see the end of what I often refer to as the "primary" table. It will be replaced by several (relatively) smaller tables, each being tasked with holding a specific type of media: music, videos, books, etc. Other tables will host data on Legos and electronics, for starters. And lastly there will be the helper table which exist primarily to normalize the database.

Whew! A whole blog post devoted to data and nothing but data. At this point some may be wondering what is up with the chairs in the title? Well, "Tables" alone sounded boring; "chairs" is pretty much an attempt at a hook to draw a potential reader in.

As always, I am hochspeyer, blogging data analysis and management so you don't have to.