Big Data, Small Data, and 42 Data

Its been a bit since I've written, so a quick update is in order.

Work had not been particularly busy as of late; my reason for the delay in writing has more to do with software than anything else. I finally had the opportunity to upgrade my laptop to Microsoft Office Pro 2010 from 2007, which has been on my to-do list ever since the hard drive replacement a while back. With the installation of 2010, I've FINALLY gotten back to working on the database.

I seem to be unable to stop referring to von Moltke the Elder, and I'm not stopping now. I found an older, saved version of the database, and decided it was not usable, so I'm starting afresh (again!) with Forty-Two, with a much-improved (I hope!) design.

Before I got too deep into this database, I thought I'd do a bit of web scraping and see if I could find a "music template" for an Access database. After a fair amount of searching, I discovered that Access templates- generally speaking- do not exist... at least not in the same league as Excel or Word templates. The best explanation that I've found for this was on an Access board, and I paraphrase here: "Access is pretty much a sandbox developer's environment. You won't find many templates. Period."

So, I'm back to doing it the way I've always done it: making it up as I go. Well, LEARNING as I go.

I suppose I should take this opportunity to make once of my periodic disclaimers: I'm not an expert, but I have a deep interest in Big Data, the Internet of Things (sometimes referred to as the Internet of Everything), data analysis, databases, STEM and the Maker Movement. Okay, back to our regularly scheduled program.

Some time ago- not long after I'd discovered the joy of caring for and feeding databases- I ran into a statement which I thought was a bit curious. It was about database design, and the author stated that the best way to design a database is with pencil and paper. I eventually understood his premise and agreed with him up to a point. My personal perspective is that this can be a great starting point, especially if you're completely new to databases, are looking at a completely new database, or if you're designing with a certain goal in mind. I've built small databases, for example, that were for crunching data in a small project (<200 data points) of mostly text. I've used existing databases and added my own queries to provide quality and efficiency reports for ISO 9001:2008 (at least, I THINK that's the spec!). There have been others as well, but my project....

Let me introduce (or reintroduce) everyone to my pet project, Forty-Two. It's called that quite simply because its ultimate function will be to answer the question, "What is the meaning of Life, the Universe and Everything?", which, for those who who are not immersed in in-print memes, is a reference to Douglas Adams' "The Hitchikers' Guide to the Galaxy".  And this was before I had even heard of Python, and Guido van Rossum's homage to Monty Python's Flying Circus. This is THE truth that I have found: I.T. and literature are strange bedfellows.

So here I sit writing about the database.

Once again back to von Moltke: the best plan can fail. Vis à vis my database. I had a grand thought for normalization: make a names table.The names table is as simple as it sounds: first name, middle name or last name- all are contained in one table.  The problem with this theory reared its ugly head almost immediately: music groups do not fit this model. So, there is a new- unplanned  table: music groups. Although the individual members of said groups may be part of the database at some shining point in the future, for now there is a groups table that just lists the names of groups- its the only way to make soundtracks and other compilations work.

That's it for now- I want to publish this entry.

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

.

Richard Strauss, and Data.

This past week, I've managed to get in to work fairly early almost every day so far (Monday- Thursday); I think Wednesday was the only day I came in later, and that was intentional. I've also been getting up earlier, and the time before work has been used to work on the database. I've made some good progress on the few boxed sets of audio CDs that we own, which is where Richard Strauss comes into the picture.

Before I go any further, I should give you a bit of my musical backstory, kind reader. You may have surmised from previous posts (especially the most recent one, Axl Rose, and Data,) that I'm more Rocker than Opera-Goer. This is 100% correct. However, my musical tastes are fairly diverse... Yo-Yo Ma, The Beatles, Chevelle, C.W. McCall, Air Supply, Deep Purple, Newsboys, Sibelius, 80's hair bands, choral, etc. I firmly believe that any music that is good should be played loud when possible.  I like some blues, classic Motown, and a smattering of jazz. I'm okay with the various forms of trance, and dance music- if it's something that catches my ear. I can even deal with disco these days. Things I pretty much have no interest in are rap, hip hop, opera and death metal.

So, what's with Strauss?

Well, the album pictured above is a boxed set I picked up at a library sale for a solitary U.S. dollar. Cheap-value-SCORE! It's a three disc set, with a booklet nearly as thick as the CD case. The 330 page "booklet" has all sorts of details, not merely about this opera, but about this particular recording, its cast and conductor. It also has the complete text and lyrics of the opera in French, English and German. When I purchased it, I had decided that the weight of the boxed set alone made it a good buy... little did I know that this was a "reference" recording, one by which all others are to be judged. Its also conducted by Herbert von Karajan, a legendary conductor.

Still, what's with Strauss?

Data entry, pure and simple. As this past week has seen a renaissance of Forty-Two, I decided to tackled boxed CD sets. The problem is that this particular set has sixty-two tracks, all of which have German titles. I'm slow enough at data entry without having to import special characters, so I did what any reasonable human being would do: I looked up the recording on Amazon, copied the track list and pasted it into Excel. From there, I copied and pasted each track into Access. That's where I stopped with music- I still have three boxed sets to go, and then it's on to albums.

In other data news, I've got what appears to be a workable solution for my internal Lego part number. It's fairly lengthy at seventeen characters, and from all appearances, this should be sufficient. I've begun the data entry on this, and tried a few trial sorts. So far, everything looks good, and this is officially stage 2 of the Peeron normalization. I still need to add dimensions and clean up the text descriptions before importing it into a table.

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

Data Intellligence... and a "slight" oops

I want to start off by stating for the record (whatever that is) that although I'm okay with doing it, data entry is NOT my forte, and it is not something I normally enjoy doing. Mind you, I'm not bad at 10-key entry- I learned it through trial-and-error, and am not bad at it still. Still, it isn't one of my favorite data activities.  And yet, in the course of building Forty-Two (my database), I find that I am tasked to do data entry at every turn.

So, ... I do data entry... because I must.

I'm trying pretty hard to keep the blog from becoming a somewhat witty changelog for my database, so I'll throw out a few speeds and feeds and then move on blog-wise.

The other day, I had just posted something on twitter, and twitter's AI recommended a few connections. One of them them turned out to be a long lost relative. I'm not going to go into the details here, but I'm kinda excited, as this is the 1st time I've run into a relative online based upon a machine learning suggestion.

WAIT FOR IT...

I accidentally pressed "Publish" instead of "Save" the other day, and I think that's a first for me. For those who read the unedited, incomplete blog post, I apologize.  In any event...

It's Saturday night in my world. My Friday at work was pretty quiet, and for the first time in several weeks I got home at a decent hour. Got a decent amount of sleep, had a great time of worship at The Bridge, and then Jennifer and I took Meerkat over to our local Walmart Neighborhood Market, where (amongst other things) we picked up a couple of their most excellent take and bake pizzas. As its late, I'm just finish this with these few updates and call it a night.

My last task for the night is some Windows Media Player updates. By "updates", I mean I'm ripping several CDs and deleting a few from the library.

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

Happy birthday, Meerkat! (*and: my Bacon number!)

I haven't mentioned Meerkat, our trusty Subaru Outback, in some time. It's a good time, though, as Meerkat's 2nd birthday was the 2nd of April. Granted, she's a bit older than that... but not much- that's the day we brought her home. And for those who may be wondering why I refer to our car with a feminine pronoun, it's merely borrowing from the naval tradition of referring to ships with a feminine pronoun.  And Jennifer reminded me that much like that day two years ago, it snowed. So, Happy Birthday, Meerkat!

Oh, and we just turned 11,000 miles (17,600 km) on the odometer. Now, that doesn't sound like a lot, and in truth it really isn't. According to http://project.wnyc.org/, the average commute for my ZIP (postal) code is around 28 minutes, which is above the national average of 25.4 minutes. My average commute is under eight minutes; on a bad day, getting caught by a train, my commute time will still be under twelve minutes.

In an effort to keep this from becoming a database changelog, I'm just going to say that work on the Lego datasaet is proceeding nicely. I haven't done anything with Forty-Two lately, but that's okay as my time has gone into the dataset.

Lastly, I'd like to introduce you to a term I use quite often IRL: "my good e-buddy". I use this most often when I'm referring to someone whom I've never met except for online contact, or someone I do know IRL, but who is more of an acquaintance than anything else. The reason I mention this is my good e-buddy @lindaregber had an interesting tweet the other day which I liked well enough to RT. She posted a study from Research at Facebook which states that there is an average separation of 3.57 for Facebook users; in other words, you are only separated from ANY Facebook user by <4 persons. That's pretty cool; I'd like to see something along the lines of The Oracle of Bacon for "ordinary folks". Although I'm not a famous person, and I have never acted, I have a Bacon number of 3, which means I'm closer to Kevin Bacon than I am to any random Facebook user! How? Alex Trebec was in Dying Young  with Vincent D'Onofrio, who was in JFK with Kevin Bacon. So, how do I get the 3? Alex has a Bacon number of 2; I met Alex in Frankfurt a.M. after trying out for Jeopardy in a Stars & Stripes sponsored event.

As always, I am hochspeyer, blogging data analysis and management 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.

Working with someone else's data

At a certain point in one's data career (plain English: pretty near every day, and with nearly every piece of data one normally touches!), you will have the "opportunity" to work with data someone else has prepared, or at least accumulated or aggregated or in some way modified. As I create Forty-Two, "most" of the data I will be using is data which I have entered (created) into a table. There is one notable exception to this, though, and it is for me a moderately to severely painful one: Lego parts.

As mentioned in a previous post, my experience tells me that the best place for my Lego data is in a spreadsheet- at least initially. And although calculations can be done in Access, Excel is much easier for me. And, they can be linked at a later date.

However, there is a HUGE caveat to the Lego data. The title refers to someone else's data. In my case, I'm using peeron.com's parts list. It's the best one that I've found, and many other AFOL (Adult Fan of Lego) sites use it.as a parts reference. The problem with the list and site is that they're horribly out of date. The list has a very standard naming convention, and the last update was nearly four years ago at the time of this writing. However, as it is the best, easiest to use and most complete list currently available, I've decided to use it. I can update newer parts as I find them on other websites.

The other issue with the Peeron data is that it is a .txt file. Not bad when importing to Excel- just copy and paste. However, to make it usable, I have to manually edit the 18K+ rows of data. As I'm in no great hurry to finish this phase of the project, it's not too much of an issue. Still, .... I know a guy (as they say) who might be able to help. More on that later.

The final issue with Peeron is that its creator strove to make it a very complete parts list. As such, there's a great deal of data which I actually don't need- stickers and superseded part numbers are two types which come to mind immediately. So, if my guy can fix my primary issue, the other issues will be much easier to deal with. If not, then I still have a great deal of work ahead of me. UPDATE: I'm not really surprised- but also not horribly disappointed- that we were not able to fix the data.

Before I forget, I wanted to post a brief update on the blog itself. I'm not quite sure when I did this last, but I think it was around the time the blog hit 10K viewers. As of today, the blog has over 15K viewers in fifty-five countries on six continents (c'mon, Antarctica!). Africa is represented by three countries, Asia by seventeeen, Australia by one, Europe by twenty-seven (I'm counting Russia in the Europe column rather than Asia), North America by six and South America by one. What's most amazing to me is that of these fifty-five countries, I could only count seven where English was either the official language, a dominant language, or one of a group of commonly accepted languages.

To each and every reader- THANK YOU!

Last: a small compilation of my blogs dealing with data (for those who are interested in how a small-time operator handles data)

http://hochspeyer.blogspot.com/2016/02/you-said-this-was-about-data-analysis.html
http://hochspeyer.blogspot.com/2015/06/data-science-pt-1.html
http://hochspeyer.blogspot.com/2016/02/a-database-against-rules.html
http://hochspeyer.blogspot.com/2015/05/data-defined.html
http://hochspeyer.blogspot.com/2015/02/forty-two-v7-or-so.html

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

Managing data better

I just discovered a fairly large data loss. As mentioned in a previous post, I rebuilt my laptop due to a failed HDD. Unfortunately (no one is looking, you can raise your hand if this has ever happened to you), I had a fair amount of data on that drive which was not backed up.

To paraphrase Hall and Oates, its gone. So, to paraphrase the unofficial motto of Chicago ("Vote early, vote often"), Save early, save often.

As promised in my previous blog, I'm going to spend a bit of time on Lego, because as far as my data is concerned, it is handled a bit differently than the other items which I am cataloging.

Lego and I go way back, but I didn't attempt to catalog my Lego collection until fairly recently. I've given consideration to including Lego in the "big database" (Forty-Two), but have found that- at least for me in this particular application, Excel is the better tool for me to use. Let me try to unpack that a bit.

Some time ago, I had the opportunity to observe how several different businesses utilized software to work with data. Some preferred Microsoft Access, and most preferred Microsoft Excel, or, to be a bit more generic, spreadsheets were preferred over databases. In only one business were both used- but independently, rather than in a complimentary fashion. The preference of software had little to do with function, generally speaking. Rather, it was more about culture and familiarity. In every case I observed, the results could have been improved simply by not just "thinking outside the box," but merely by thinking.

In my case, a spreadsheet seems to be the best solution- based upon my experience. My primary reasoning behind this is because Lego is a single thing which does not need to be linked (or, related) to anything else. And, even though I may be interested in a bit of analysis of the Lego "population", in the larger scheme of things Lego exists in its own unique bubble: shapes, colors, themes, sizes. I could build tables based upon these and other categories, but once again, they would relate only to Lego.

Forty-Two, on the other hand, illustrates quite well the differences between a flat database (the Lego spreadsheet) and a relational database (Forty-Two). With the Lego spreadsheet, I can see a snapshot of all the facts of my Lego collection. But with Forty-Two I can write an ad hoc query to tell me where all Lord of the Rings media is located. It would show where each book, soundtrack, game and video is located. It would also show the number of copies in a given format. It would also tell me the last time the media was viewed.

There's also one additional aspect of Lego databasing which is unrelated to software, but makes cataloging it so much easier: storage.

I'm an AFOL (Adult Fan of Lego). AFOL is a title; more of a descriptor, actually, as it really doesn't carry any of the clout that, say, CCNA carries. Still, it differentiates me from most adults who play with their kids while playing with Lego. And, its somewhat hard to say that without sounding like some sort of pompous jerk, because it sounds like I'm slamming parents who "play" Lego with their kids. Quite the contrary! If you're a parent who engages with your kids over a pile of Lego, kudos to you!

An AFOL, though, uses Lego as their primary creative medium. There are professional Lego artists out there who make a good living by building amazing models for corporate clients. There are also educators who use Lego in either a standard classroom setting, or in a program for ASD (autism spectrum disorder) kids. Even architects use Lego for models. And although each of these examples is an example of adults working with Lego for a living, the typical AFOL is something else.

They're a bit of a subject expert on Lego. They probably have some advanced building skills. But mostly, I think, they are something of a type of a Leonardo da Vinci. They are probably the precursors of the maker movement, which is another subject entirely.

Back on track: AFOLs need organization, and the English word for this is storage. Over the course of many years, many bricks (elements) will be collected. I've found that the (current) best way to keep track of these is to put them in plastic bags (connected in groups of 10) with a card inside indicating the count and the date of the count. These, in turn, reside in drawer organizers.   

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

Feeds, Needs and Speeds

Well, it's starting to get real. The database, that is. As I posted earlier on Twitter, 200 rows of data in a single column do not a RDBMS make, but it's a start (the count is currently 224). As Jackie Gleason quipped in one of his signature lines, "And away we go!"

The casual reader may be wondering at this point: there are actually folks out there still building relational databases? Isn't the non-relational NoSQL model more popular in terms of new deployments, versatility and just plain coolness?

I'd like to explain a bit of my personal journey that brought me to Forty-Two, the database.

Long ago, like teenagers everywhere, I faced highschool graduation without a plan. Not just a clear plan, mind you- NO PLAN. Computer Science was in its infancy at the time, and nearly nonexistent in most high schools. I was not one of the cool kids, nor was I a jock or a brain, but I was also not a nerd. However, I knew one or two nerds. And the nerds were highly focused in their scholarly discipline: they were not into math and science; they were into math. They were geometry slingers, wearing leather slide rule holsters on their hips. They had glasses, thick glasses. Below average complexion. Few social skills. Pocket protectors in their left shirt pockets. And behind the pocket protectors... punch cards for their next "program".They were the late 70's analogues of Drs. Sheldon Cooper and Leonard Hofstadler (The Big Bang Theory).

I was not them. Well, sort of not like them. I liked history. I was (probably) the worst kind of history nerd: I was a military history buff. I started out with Avalon Hill games like France 1940 and Panzer Blitz, and progressed to Tobruk and Squad Leader, eventually culminating in the non-Avalon Hill classic Fletcher Pratt's Naval Wargame. The point is this: as the complexity increased, the playability decreased, as did the number of folks willing to take on the rules. But, I digress.

I was accepted to Rosary College (later Dominican University). I declared history as my major, and spent two unremarkable years there. I eventually had five colleges or universities under my belt, with no undergraduate degree to show for all of the buckazoids invested.

Long before this became a mainstream theory in education, I discovered that we do not all learn in the same way, and that higher education was not really the best choice for me.

Fast forward a few decades. I've mentioned this fairly recently- I was working as a data analyst at an "action sports" company. The company designed paintball equipment, and had it manufactured overseas. My job as the data analyst was to take Wal Mart RetailLink data and dice and slice it into what my employer could use.

The problem was my employer was using either Office 2000 or 2003, which limited Excel to a maximum of ~64K  (I believe it was 63,536) rows. As time went on, my data often exceeded this artificial limitation, and I was forced to use Access just to grab the Monday morning data. Once again, skipping several steps, I became adept at moving data between Excel and Access.

Fast forward one more time to today. I use all sorts of tools to do my job; Access and Excel aren't really part of my professional portfolio of commonly used programs on the job, but I use them at home,.. pause for effect.

Yes, although I may have mentioned a bit about Forty-Two before, I don't think I've said too much beyond it was my own little database dev world. Here's where the title comes in: way back when I was an I.T. reseller, we used to often qualify sales by talking about speeds and feeds- equipment specifications. Needs are also important (besides completing the alliterative trilogy).

So, Forty-Two is an obvious reference to Douglas Adams works, and is so named because its' goal is to answer that elusive question: what is the meaning of Life, the Universe, and Everything. It is starting out life as an Access database, currently with only one table. Previous iterations have taught me to take it easy with adding tables, so my aim is to get this Titles table to be mostly complete before adding additional single- or very few-column tables and then finally starting to create the relationships.The first table is called Titles simply because it holds titles: books, videos, software... if its media, then its name goes here. Why? Forced normalization: why go through the normalization process when I can start out with a relatively clean dataset?

This is starting to turn into a wall of words (by my standards, anyway!), so stay tuned... Lego is next!

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

Data, defined

Although it is not my intent, I am certain that this post has the potential to step on a few toes, possibly bruise an ego or two, or ruffle some feathers. I may even get someone mad. Really e-mad.

For starters, I do not have any letters, diplomas, certifications and am not currently professionally employed in whatever one might consider the "data community". Whatever that might be. I do not claim to be an expert or have any special expertise or training in the areas of Big Data, the Internet of Things/Everything, Statistics, Analytics or The Cloud. I was once employed as a data analyst working with Small Data for a short time.

Whew!

So, who and what exactly am I?

I'm a guy who tweets (and retweets) primarily on the subjects of Big Data, IoT, programming and related topics. As far back as high school- maybe even earlier- I've been interested in data. It was either my music collection or Fletcher Pratt's Naval Wargame that gave me my start in classifying and quantifying. I remember even attempting to do a few music surveys way back when, and some of the respondents were unhappy because the polls were not simple popularity contests, but the answers were weighted based upon their position on the poll. Fast forward to today. I'm currently building a flat database of my Lego collection in Excel 2007 (why 2007? Because that's what I have on the computer nearest to the Legos!). This, in turn, will be added to my master database Forty-Two- so named because it answers the question of Life, the Universe and Everything.

Having said ALL of that, I'd like to start off by saying that the term "data" may not be as concrete as we are lead to believe. In my world, data comes in the following flavors: Big Data, Not-So-Big Data, Small Data, Micro Data, and Statistics. Depending upon the size of the dataset(s) and one's perspective, most- if not all- data can fit into more than one classification. Really? Sure. Case: say there's a hypothetical high school senior who is one of the stars of his basketball team. He's a good defender, doesn't get a great deal of fouls (below the league average), and is about average in scoring- except he leads the league in free throw percentage. Several colleges and universities are interested in him- they've got data on this fellow going back to 6th grade. That's data- to them. To me, a person who could care less about basketball- it's nothing more than a bunch of irrelevant stats. On the other hand, these same scouts would not be impressed by the number of PhD's that follow me on Twitter.

So, how big is a Big Data dataset? I asked a coworker. He wasn't sure, but thought a mail list might qualify. Don't laugh too soon- some of the mail lists I've seen have more than 10 million names. To me, though, I'd put that in the Not-So-Big Data or Small Data categories. The IoT,  Amazon, Google, Youtube and Wikipedia definitely fit into the Big Data category, but to the average person, these can be tough to visualize. So, for what I think might be a decent, understandable Big Data dataset, I propose the 2010 U.S. Census. It was a 10 item questionnaire (with a few extra answers possible) that mailed to 135,000,000 addresses representing approximately 309,000,000 persons.

Small Data could be a database, a website or the phone directory of a small to medium sized city- the lines are pretty fuzzy here.

Lastly, there's microdata. I'm not sure if this term is used anywhere else, but I find it to be a convenient term for personal data- data generated and maintained by one person or one family for their own use and not often formally shared. A cataloged collection of coins, stamps, recipes, exercise/workout logs or Legos- all of these are Microdata in my worldview.

Thanks for your patience- I hope you enjoyed this. I generally write a lot less... I'm not a fan of writing or reading walls of words!

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.

Installing update 1 of 160

I can't make this stuff up. After what was possibly the oddest birthday observance of my life this past Friday, I was doing a bit of tinkering on my netbook, updating the old antivirus via a flashdrive. With the software updated, I decided to shut the little computer down and head off to bed. Well, I'm writing this (cue the "Total Recall" scene where Arnold is watching the video with a wet towel wrapped around his head, and the Arnold on the video says something like, "If you have a wet towel wrapped around your head and are watching this,....), so what I had planned to do didn't happen. I just took a look, and its up to update #23. It's a fitting bit of punctuation to put on the end of this week.

I started writing this a few days ago- followers of this blog will probably understand the delay- but this time, it isn't me! We've had some weather come through our area that has just been plain unpleasant. First, a 12 hour power outage on my birthday, and then yesterday (Sep 10) a six hour outage.

Uncool. Very uncool. As I'm fond of saying, Commonwealth Edison- the electric company- is off of my Christmas card list! On the plus side of things, Jennifer and I have gotten quite adept at rapidly setting up- I think we had power to our sump pump in ten minutes or less. It turns out we're going to have to replace the battery and possible the charger as well for the backup pump. And this has what to do with data, you might ask? 

Everything, actually. The power had gone out around noon, and everyone was ambling around the house like extras from Shaun of the Dead. At around 1400, I had a brilliant notion- my laptop was charged... I could work on my database for a bit. Buoyed by this happy thought, I slipped on my neon yellow-green Speedo foam sandals and proceeded down the stairs to the Secret Underground Lair, my way lit by a motion-sensing battery powered overhead light. As our winters can be quite cold, the way to the SUL is paved with interlocking foam exercise mats. And since this is a pathway, there is only a single row of mats until one reaches the exercise area directly outside the SUL. I discovered something was frightfully amiss when I put my first foot down and there was a lot more "give" to the mat than there should be. My second step triggered the light, and also showed little waves in the water that was on both sides of the floating mats. The sump pump battery had failed.

"Get some shoes on- there's water downstairs"

Jennifer only heard the last part as she was in another room. I grabbed the garage keys and started the generator. I plugged the supply cord in and brought the distribution box into the house. I went back outside, grabbed the 50ft (15.24M) extension cord and plugged it in to the distribution box. I turned around and... remember when I said Jennifer probably only heard the last part? There was a disembodied pair of socks and slippers in sight.

To summarize The Almost Flood of Sep 10, our basement was saved by Forty-Two, which is, as everyone knows, the Answer to Life, the Universe and Everything.

As long as I'm on the topic of data (that's allegedly the theme of this blog, right??): a revelation, education and the birth of a database. The revelation: I'm pretty sure I'd mentioned this previously: I'd experienced a problem with at least one of my tables a few months back- I was suddenly unable to enter or modify data. It got so bad that I ended up starting a discussion on LinkedIn and eventually deleting the table and starting from scratch. As I was doing some research for the new database I'm building at work, I ran across a tidbit in Matthew MacDonald's most excellent Access 2007: The Missing Manual, and apparently I had inadvertently locked the table(s) in question.

We were working on a job and had to make a few corrections to it before spooling production, and one of my coworkers mentioned that we would not be experiencing this particular issue if we had a database like he had at a former job. So I started to build a few tables and it dawned on me that a form would be needed. As I rarely use forms, I decided that now would be a good time to expand my horizons. Forms are deceptively simple to make (in some ways) in Access 2007: open a table, goto the CREATE tab, and click on Form. Its great for really simple data entry forms. I made a simple one and the only modification I made was to the size of a few fields. Next, I did the same thing for the Music_Recordings table.

The band stopped playing. The storm clouds gathered. A wizard appeared and started shouting, "Papers, please!", in Klingon. It went from fun to work in an Augenblick.

That's all dramatic license, of course. For a "hobby" database, some of the tables are fairly wide- hence, the need for forms. For example, each record in the Music_Recordings table has seventy-six data fields. When I looked at what had been created, Access had a form with two columns of thirty-eight fields each (and labels). I tried all sorts of things, but could not get the fields where I wanted them. That's when I picked up Access 2007: The Missing Manual at the library. It was there that I discovered the most unlikely solution, pretty much irrational. To move an individual field in a form in Access, one must "REMOVE" it from the grid. That sounds pretty ominous and illogical to me, but that's how it works.

My form looks nice now. Not perfect, but getting there.

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