So I’ve spent the past couple nights drinking herbal tea and brushing up on my database design principles. I’m slightly ashamed to say that I never even touched databases in college. The entirety of my SQL/Database skillz come from “the field”. However, now that I’m once again on the job market, I figure it’s probably a good idea to brush up on things like design theory terminology, such as “first normal form”.
Something that I’m noticing is that I’m completely ignorant of some of these terms, but the concepts already are very close to my heart. Take, for example, the above mentioned first normal form.
A table of the first normal form 1) has columns that contain only atomic values and 2) has no repeating groups. What? Okay, atomic forms – another term I’m unfamiliar with. Repeating groups? What’s a group – do you mean no repeating rows? I don’t know… I’m confused. After digging in (i.e. reading down the page of my book where the example was illustrated with a picture), it all made sense.
Atomicity: Atomic values are values that are perceived to be broken down as much as reasonably possible. For example, names in the database in the a single columns “Firstname Lastname” are not atomic, while having a column “Firstname” and second column “Lastname” is atomic. Obviously, you can break it down more (by letter), but that’s unreasonable.
Repeating Groups: An example of a repeating group would be the case where you have a table, listing Book-Id, Book-Title, and Authors, where you have the columns [BookId], [BookTitle], [Author1], [Author2], [Author3]. The repeating groups are the 3 author columns.
After I came to understand what was being said in the definition of the first normal form, I thought “What a waste of my time. This is so painfully obvious!” I mean, from my “field” learnings, I can testify to how painful it is to deal with non-atomic columns and repeating groups. I felt like this definition was analogous to having a section in the auto-manual that stated “It it good practice, when temperatures fall below freezing, to utilize your vehicle’s atmospheric-entropy-elevator, or aee device, to maintain the homeostatic environment of the vehicle“. I mean, have you ever actually tried to deal with a database with a single name column where some names have been entered as “FirstName, LastName”, others as “LastName, FirstName”, others as “FirstName MiddleName LastName”, and even “LastName, MiddleName1, MiddleName2, MiddleName3, FirstName, Junior/Senior”? [..oh God, bad memories flashing back...]
Now, this is a cynical outlook; it’s actually quite good for me to revisit these concepts in formal and academic style. I’m curious how many other people fall into this boat with me, and how our applications will vary from those of people with more formal database backgrounds.
In my random googlings, I stumbled upon this: Ten Common Database Design Mistakes. Most of these seemed pretty “common sense” to me, but I thought it was excellent to see them laid out in text.
Database From Hell, via teejayhanton on Flickr. "this was at my old job. it was a database brought to me from a government agency (unclassified). my task was to a) sort it out and b) create a web application that would plug into it."
