23 Sep 2003 cm   » (Journeyer)

Databases are Dumb

Lately I've started to be really bugged by how dumb database constructs are. Let's take the table as and example. I sometimes would like a table to tell me how many rows it contains. Alas, the table doesn't know, it rudely ignores me. Long ago I discovered that tables will only talk if SQL asks the question. So I learned to formulate a query to SQL that it passes on to table, and gives me back my answer. I don't like the middleman so much. I can't ask, "table, how many rows?". I have to tell SQL to ask table to count something. I don't know why, but I can't tell SQL to count the rows, I have to ask it to count either a column, or all columns. That's really dumb to me because, don't all rows in a table have the same columns? Why do I have to specify one column, or all columns (*), when I just want a count of rows?

Maybe I'm being too demanding, insisting that a table know about its contents. Tables are made up of columns. "Table, how many columns do you have?", I ask. Then I find out that table won't even tell me that. I have to ask this other table to tell me what's in the first table, and I still have to tell SQL how to ask the question for me.

Well, after a while I get tired of trying to get tables to talk to me. I try to get columns to talk to me, but they're even ruder than tables.

Then I have a little light bulb go on. Maybe instead of asking the tables, I can ask the content of the tables. After all, that's really what I'm usually interested in. I know there are fifty states in the United States, but I forget what the capitals are. So I try that. I decide I'd like to ask, "States, what are you capitols?". We'll there's a table in my database that I'm told has all the states, but it turns out there's no way to ask the states anything. I can ask the table to tell me what's in it, but only by telling SQL to ask for something. I'm getting really frustrated about now. My SQL guide tries to be helpful by telling me it can find out for me how many states have capitols that start with the letter 'A', but I'm not interested in that kind of aggregation. Sometimes my geography is bad, so I can't even point to the state that I want. I always get New Hampshire and Vermont mixed up (sorry about that). So I'd like to say "States, whichever one of you is Vermont, speak up and tell me your capitol". But again, I am back to asking SQL to ask the states table something about Vermont and it's capitol. SQL is really very picky about how I ask, too. Anyway, it turns out that the States table doesn't actually know anyway, all it knows is that there's another table that knows the names of all the cities, and can tell me if a city is the capitol. Talk about pointing fingers! After all that, I finally figure out that I can tell SQL to ask the States table if any of its states are named "Vermont" and if there is one (which I already know exists) to ask the cities table for all the cities in Vermont which are its capitol -- even though I know a state can only have one capitol. What a struggle. All I really wanted to ask was "What's the capitol of Vermont?", and after several false starts I discover I have to ask a whole bunch of different people about all kinds of things I don't care about and narrow down the results to the one thing I do care about. What a waste!

Latest blog entries     Older blog entries

New Advogato Features

New HTML Parser: The long-awaited libxml2 based HTML parser code is live. It needs further work but already handles most markup better than the original parser.

Keep up with the latest Advogato features by reading the Advogato status blog.

If you're a C programmer with some spare time, take a look at the mod_virgule project page and help us with one of the tasks on the ToDo list!