After many hours of frustration with the apparent broken ports/install on a FreeBSD box at home I decided to forego the access -> ODBC -> Postgres idea and just play, for now, with Access.
I decided that I wanted to build a little 'generic object' construction tableset so I could describe all the objects that I owned. Then I could assign them locations, loan status and all sorts of useless information.
Building the tables was easy. Just create a table, set up the fields and tinker with their properties. Creating the references for referential integrity took a minute with the HLP file and a minute with Google but I finally found it - a GUI for, literally, drag-n-drop referential integrity definition.
It was gorgeous.
Now, before you call me a n00b let me just rant a little.
I know Access has its faults. I've used it in the past but thankfully it was 1996 and I've forgotten all of my clue. But it was simple. For someone like me who has spent a few years hand-building SQL tables, carefully crafting web-UIs for accounting databases, service management databases and all kinds of tedious code-stuff I was relieved to be able to just drag-and-drop stuff around. I could see, instantly, the relationship between my tables. And modify it. Admittedly the many-many RI linking is new to me. :-)
And here's the crux of today's post - knowing about "good" relational database design, understanding referential integrity and having experience hacking out mountains of code makes this kind of application development environment very powerful. I know that Access will take my queries and rewrite them into random gibberish, playing havoc with ODBC-interfaced databases unless I'm careful. I know all about Access' performance issues. But, that aside, it mostly works.
In short, I plan on having a whole heap of fun tinkering with database stuff. Because, after years and years of hacking, I realise that this stuff should be easier but easier doesn't mean you should forsake learning the actual theory and burning yourself first.
Now, on a side note, does anyone know of any opensource style schema/report engines that don't suck? I've come across a few written in Java which I'd pay the price on if they didn't require so much system resources just to run.
Then, does anyone know of any useful GUI open source form and reporting utilities? there has to be a better way of developing web-frontends for database applications without writing
mounds of php.