Database Design

I don’t have to design the application, I’m simply building a new version of an old DOS chestnut.

If I can imagine the code for the original Agenda, about half of it runs the user interface, and the other half implements a toy Database Management System (DBMS) that manipulates the data. So I start by focusing on the data side, laying out the databases.

Agenda originally used text files. Beeswax used XML, which are hierarchical structured text files. There’s a neat trick with XML that you can transform files on the fly into web documents, but Beeswax wasn’t doing that - it simply stored items and notes using tags.

If I want to store thousands of records, text files aren’t going to do it for me. They are too fragile, too easy to break, and I need to write all the handling myself.

The key advantages of using a DBMS is that they are hugely scalable, and that they already have all the searching, sorting, matching, and triggering code built. And they support views, provide transactional integrity, and let you back up your data without taking the users off-line.

My ‘design’ work is trivially easy. I run the old program and walk through the Agenda functions, one by one, deciding what the ‘building blocks’ are. I check out the ‘Options’ screens and the help files, which tell me what my data elements are. Here’s my list of tables:

FILES - a list of projects for each user.
VIEWS -
SECTIONS -
COLUMNS -
ITEMS -
CATEGORIES -
ASSIGNMENTS -

I’ve added fields so that multiple users can share the same database (what’s the fun of a single-user website). The USER table will come from Joomla! (I’ll talk about that part and the user interface later).

Since I want to add an UNDO function (the original Agenda didn’t have one), I’ll add another table for UNDO. That’s about it.

I’m going to start without using database triggers, explicitly running the trigger actions from my code. And I’m not going to use any views until I understand the performance issues better (a better way of saying this is that these tables will be my ‘view’, and I’m going to implement the underlying schema in the simplest fashion possible).

If I was really a geek, I’d use an Object-Oriented database (OODB). But they are pretty specialized, and I don’t have one handy. Instead, I’m simply going to wrap the database access layer with code that provides object-style handling.

The data half of Agenda feels almost written, although I haven’t touched a line of code yet.