Experimental Thoughts

Developing a database application means converting your data into types understood by the DBMS, generating SQL code, sending it to the DBMS for evaluation, retrieving the result, and converting the result into types understood by your application language. It’s even more strange when you use parameterized SQL: you generate some code with placeholders instead of literals, and then you send the literals separately (by “more strange,” I certainly don’t mean “worse,” — parameterized SQL is a huge improvement).

If this design were suggested for any purpose other than interacting with a DBMS, others would react with immediate (and justifiable) suspicion. The use of “eval” is strongly avoided, and many popular languages don’t even support it. So why is this design so widely accepted for database management systems?

(more…)

Here are a couple common representations of data sets:

username | realname   | phone
---------+------------+----------
jdavis   | Jeff Davis | 555-1212
jsmith   | John Smith | 555-2323

or

<users>
<user>
<username>jdavis</username>
<realname>Jeff Davis</realname>
<phone>555-1212</phone>
</user>
<user>
<username>jsmith</username>
<realname>John Smith</realname>
<phone>555-2323</phone>
</user>
</users>

(more...)

Most application development can essentially be described as formalizing a business process. You take an informal process, remove the ambiguity that’s invariably present, adapt it to computer interfaces, and restate that process in a formal language. This has many benefits, among them:

• Automation – This is the most obvious benefit of using a programming language to formalize a process. The computer can now understand the process, and can execute the process without unnecessary human intervention.
• Unambiguous – Ambiguity can be removed without actually creating an application, but you can’t create an application without removing the ambiguity. Some of it is removed through discussions with others involved in the project, but most is resolved through the intuition of the application developer.
• The ability to handle complexity – Simple processes can be handled correctly by humans using intuition, rules of thumb, and following patterns. But complex processes are much easier to solve with a formal process that has independently verifiable parts.

I recently read the following article, another opinion in the long-standing surrogate key vs. natural key “debate”:

http://rapidapplicationdevelopment.blogspot.com/2007/08/in-case-youre-new-to-series-ive.html

I put “debate” in quotes because it’s filled with so much confusion, I don’t think most people understand what they’re arguing over. People use different definitions for surrogate keys, often in the same discussion, so for the purposes of this article I’ll define surrogate key to mean: “A system generated key that is not derived from the business rules, hidden from the business, but visible to the application”. The surrogate key is used to take the place of a natural key, which is derived from the business rules. In other words, a surrogate key is a pointer.

(more…)

My employer is evaluating RT. So, we have a test instance set up, of course, and it’s in a working state. The application is written in Perl with the Mason framework, it has support for many databases (including PostgreSQL), and generally has a decent feature set.

We need to migrate from our previous system, so we need to import the data into RT. RT’s tools and APIs do not meet our importing needs, so we look into the schema to do the import directly. I decide the easiest way to figure out how the schema fits together is to just turn on query logging. So, we reload the page, and all of a sudden a deluge of output appears in our “tail -f”. At first I thought it was just the normal number of queries generated by some abstraction layer, and we could dig through them. But it was about 700 queries.

Hypothetically, if I were to use a much simpler database (let’s say SQLite or BerkeleyDB) for a project, rather than a powerful RDBMS like PostgreSQL, what would be the first thing that I would miss from PostgreSQL? Not “miss” in a theoretical, long term sense, but “miss” in that it would cause a practical impediment to actually constructing even a prototype application?

Would it be triggers? Certainly not. Server side functions? Those would certainly be missed, although may projects go along fine without them. SQL’s relational algebra and calculus that make a relational database relational? Maybe, that would certainly be preferable to procedural code using BerkeleyDB, but I don’t think this would be the first thing I’d miss (digression: actually, it wouldn’t be missed until later, when I was actually trying to debug a difficult problem or look at the data for analysis).

(more…)