The beauty of the relational model

Friday 8 April 2005

Relational databases are great. They've become the hands-down dominant database technology primarily because of the high level of abstraction they provide. ADO.NET provides a great set of in-memory relational data classes, for example, DataTable. Why couldn't they have understood the relational model better and provided better abstraction?

I think relational databases work as well as they do because they provide a simple abstract data model (rectangles), and it doesn't matter where the data came from.

Dealing with data in pure rectangles means that data can be used in many different ways. To begin with, a table is a rectangle: every row has the same columns. Then, the result of a SELECT query against the table is a rectangle: it has a subset of the rows, and a subset of the columns, so the result is another rectangle.

The designers of SQL understood the power of the abstraction of rectangles. That's why you can query a table:

select a, b, c from mytable where d = 17

or you can query the results of another query:

select x, y from (select a+b x, c*2 y from mytable where d = 17) where x > 23

If a query is really useful, you can create a view from it. This creates a new named data source alongside your tables:

create view myxyview as select a+b x, c*2 y from mytable where d = 17

Now you can select from the view as if it were a table, and the rectangles just work:

select x, y from myxyview where x > 23

And of course, since rectangles don't care where they came from, you can create a view based on another view, or a select from a select based on a view, and so on, ad infinitum.

Going even further, joins and unions provide ways to combine rectangles side-by-side or serially. Most databases leverage the power of rectangles further by providing built-in "tables" which expose internal information about the database and its schemas. The relational model is fundamentally about rectangles and their interchangeability. Their simplicity and universality is at the heart of relational databases' success.

Comments

[gravatar]
andrew 10:56 PM on 8 Apr 2005

Is there something missing here? I was anxious to read a good rant about ADO.NET and the article just ended!

[gravatar]
christopher baus 11:05 PM on 8 Apr 2005

I was thinking the same.

[gravatar]
Damien 11:52 PM on 8 Apr 2005

It's a cliffhanger. Tune in next week when myxyview discovers mytable is being queried several times a day by ADO.NET and was only being used for those sexy inner joins.

Also witness the shock when it's revealed that d will never, ever be equal to 17.

[gravatar]
Ned Batchelder 8:35 AM on 9 Apr 2005

Oops: sorry guys, I published before I meant to. "The disappointment of ADO.NET" is now another post.

[gravatar]
Peter Herndon 2:43 PM on 11 Apr 2005

For what it's worth, I tend to think of relational databases in terms of sets and Venn diagrams.

Add a comment:

name
email
Ignore this:
not displayed and no spam.
Leave this empty:
www
not searched.
 
Name and either email or www are required.
Don't put anything here:
Leave this empty:
URLs auto-link and some tags are allowed: <a><b><i><p><br><pre>.