Some Database Ideas

Beyond RDBMS's

The traditional RDBMS just isn't R enough. Why? Well, if you go and render everything into 3NF, you usually end up with a very safe and consistent database, with no duplicated information, but you also end up with a database that's hard to use.

The problem is, traditional RDBMS tools like to do joins to do the R part of RDBing. Joins are good if you want to produce a third table; there's nothing wrong with joins in that case. But sometimes what you really want to do is get several detail records for one main record. And sometimes you want a particular join to sort of be there, somewhere, for when you need it. So you put cascading-deletion and restriction constraints, and referential-integrity constraints, and you still end up with big honkin' SELECT statements, only one one-to-many relationship per view (or form!) and clumsy forms.

The problem is this: your JOIN column isn't just any random column in the two tables you're joining. It's a column that contains cookies from some namespace, and it's the same namespace for both columns. And, usually, it's a primary key in one of the tables you're joining. And, usually, the table it's a foreign key in knows which table it's a primary key in.

So why not specify these things? When you create a primary-key column, say that it's a primary-key column from namespace And when you create foreign-key columns, say that they are foreign-key columns from that same namespace, and give them a pointer to the table they are detail records for. Then, instead of saying:

select, payrec.week, payrec.hours from employee, payrec where employee.num = payrec.empnum;
you can just say:
select, week, hours from payrec;
or maybe:
select empnum->, week, hours from payrec;

You should also specify whether deleting a record from some table whose primary key is in such-and-such a namespace is the same thing as deleting the primary key from the namespace. That is, are there other tables (perhaps at other companies) that might be keyed to the same primary key?

It would also be very nice to be able to do lookups on remote, networked tables; this idea would facilitate that, as long as you agreed on the names of namespaces.

Queries would have to be a little different to take full advantage. The simple SELECT of SQL must go by the wayside; it outputs a table in 1NF. What I'd really like to do is say:

please list the author.names, subject.names, volume.(callno, status)es, and title for bookid 4101;
... and get a record with zero or more author names, zero or more subject names, zero or more volume records, and exactly one title in it.

More to the point, I'd like to be able to run a similar query on a whole list of bookids and get results. And I'd like to put it in an onscreen form to be edited, to add new volumes, for instance.


OK, it's an idea, so what? I need to do something about it. Like implement it or something. I haven't done it yet. (Last modified Thu May 22 23:53:14 EDT 1997)