blog.josephhall.com

I was reading an AJAX tutorial the other day, and something that the author said caught my eye:

"All we really need is the title, but I always provide a primary key for any table that I create."

Why add it if you know for a fact that you don't need it? In this case, as with every case I've seen so far, the primary key in question was an auto_increment integer. I have long maintained that while this is necessary in most tables, it does not necessarily belong in every table. What you really need is a unique identifier. Without this, all you have is a jumble of data that really doesn't make sense to be stored in a database.

But that unique identifier doesn't always need to be a counter. Let's take a look at a couple of examples. Consider the following hypothetical user table:

CREATE TABLE users (
    username  VARCHAR(50) NOT NULL,
    realname  VARCHAR(50) NOT NULL,
    password  VARCHAR(50) NOT NULL,
    PRIMARY KEY (username)
);

It makes sense for a username to be unique, because the username/password (or other token) combination will be required for access to this application. If you need to refer to this table from another table, you can just refer to the username, because it is unique and identifiable. I might note that MySQL itself does not use an auto_increment field for its own user table.

There are potential problems with this, however. When you change a username in this table, you need to change it in any tables that reference it as well. Additionally, you are taking up a little extra (if negligible) space in referring tables. An auto_increment uses an integer, which takes up less space, and will not change under normal circumstances. It should be noted that Unix-style operating systems use a UID to identify not only users, but file and process ownership. The username itself is rarely used by the system for anything other than making things more human-readable.

Let me show you a table structure that I've been working on this morning. I have a need to store recipes in a database, but because a recipe contains varaiable numbers of ingredients and directions, it doesn't make sense to try and store each in its own field in a single recipe table. Instead, I have broken out my structure into three separate tables:

CREATE TABLE recipes (
    recipe_id    INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    name         VARCHAR(255) NOT NULL,
    source       VARCHAR(255) NOT NULL,
    preheat_qty  INT(4),
    preheat_unit ENUM('F','C'),
    yield_qty    INT(4),
    yield_unit   INT(11) NOT NULL,
    PRIMARY KEY (recipe_id)
);

CREATE TABLE recipe_ingredients (
    recipe_id      INT(11) UNSIGNED NOT NULL,
    rank           INT(4),
    name           VARCHAR(255) NOT NULL,
    qty            INT(4) NOT NULL,
    unit           INT(11) NOT NULL,
    PRIMARY KEY (recipe_id, rank)
);

CREATE TABLE recipe_directions (
    recipe_id      INT(11) UNSIGNED NOT NULL,
    rank           INT(4),
    direction      TEXT,
    PRIMARY KEY (recipe_id, rank)
);

The recipe itself is referenced by a unique counter, which is usual. INT(11) contains far more unique identifiers as I expect to need for that table. But there will be multiple ingredients per recipe, and multiple directions per recipe. Even if I only store recipes with no more than 4 ingredients and 4 steps (which is unlikely), I need 4 times as many unique identifiers per table.

I already have to store the recipe_id, to keep from orphaning the data. It's important to store what order each step is in, because if they got out of order, the recipe would quickly becaome confusing. It's nice to store the order of ingredients in the order in which they'll be used too, and many people write recipes with this in mind. I've called this field "rank". Since I already have those two fields, and they already uniquely identify the rows, why not officially make them primary keys together? MySQL allows it, and I'm going to make use of it.

I propose that whenever you create a table, you take a moment to consider whether or not you actually need a counter. Most of the time you will, but not always. Get out of the habit of doing things because that's the way you've always done them, and get into the habit of doing things because you've thought about them and have made an informed decision specific to the situation at hand.

Buzz off social networks!

Posted using ShareThis

Dedicated to eightyeight.

Don't get me wrong, I like Google Reader. Every other RSS reader that I've tried to use is comprised of at least 90% suck, whereas Reader only seems to be about 10% suck. Most of that suck has to do with its social networking "features".

I was surprised yesterday to find a follow invite from my brother, in Google Reader. This is because a) he hates social networking at least twice as much as I do, and b) I was entirely certain (and later confirmed) that he's never opened Google Reader. As I poked around, I discovered that I was somehow following 27 people. Four of them had apparently accepted an invite from me, and the rest had been sent invites that had not yet been responded to.

Let me be clear: I have never sent a follow request in Google Reader. Google automatically sending invites to people on my behalf feel a little like when I hear people say things like, "Oh sure, Joseph would love to come to your <INSERT EVENT HERE>." Would it kill you to ask me first? One of the people who had been sent an invite was a person who had sent me a business proposition, which I respectfully declined. That was to be the end of my contact with her, but Google was so kind as to send her a Google Reader invite for me. Gee, thanks.

Those who have tried to unfollow have probably found yourself frustrated with the fact that there is no interface inside of Reader to unfollow a person, or to turn off sharing altogether. Worry not, I figured it out.

• From inside Google Reader, click the "Settings" drop-down in the top-right corner.


• Select "Google Account Settings" (not Reader settings). This will open a page where you can edit your profile.


• Click on "Edit Profile". There's nothing useful on this page, so click "Save" or "Cancel" at the bottom (intuitive, isn't it?).


• The next page has a link that says, "<YOUR NAME> has X followers" and another link that says, "<YOUR NAME> is following X". You can't stop people from following you, but you can click that second link and stop following people. These are the ones that Google sent invites to without your permission.

Of course, it would be nice for Google to give you the option to disable sharing altogether, so that you could just use Reader as a regular RSS reader. One day I'll find one of those that doesn't suck.

People are probably sick of me complaining about stuff like this, so I'll keep it short.

Google Buzz showed up in my Gmail account this morning. I tried it a little bit, read some feeds, gave it a shot. At the moment I find it more annoying than Twitter, so I've turned it off. You can too!

Clicky!

Maybe I'll give it another shot later. For now I've shoved it back into the depths from whence it came. It can share rent with its other dejected roommate, Google Wave.