What to tell your wife
You can tell her, databases ares nice but they are no competition. When they are, you have a problem, not your wife.
:)
GerardM

Foreign keys
Foreign keys are perfectly possible in MySQL. I use them all the time. See The InnoDB Storage Engine.

DB abstraction class
The beauty of a database abstraction object is that it can work with many database implementations with little or no changes to your existing code. Got DAO? :P

You can buy the cow, *and* get milk for free from another cow altogether. Life is good. :)

DB abstraction class
MY DISAGREEING PART:

I disagree. Perhaps if writing a general-use piece of code meant for webmasters everywhere to run, no matter what their database. But if you're doing high-performance stuff, you want to optimize for the database engine you've chosen.

(In an analogy example : designing for portability is not always desired. I'm glad my house does not have wheels.)

It's not like serious sites switch database engines every week or so, just for fun, and need to be able to flip around easily.

If I switch CD Baby to PostgreSQL, it'll be a MAJOR switch, including a total restructuring of the database and my approach to accessing it. I'd use views. I'd use foreign-keys. And if there was some new fancy PostgreSQL-only feature that would really help me, I'd use that too, without fear of it not being portable to MySQL or SQLite.

I like Jeremy Zawodny's post called Database Abstraction Layers Must Die. Start reading halfway down under The Portability Fallacy.

MY AGREEING PART:

All that being said, in future rewrites I am looking forward to keeping all of my SQL in one place, instead of sprinkled throughout the entire system.

In that sense, then yes if I had all my SQL in one place, accessed by functions that called their purpose such as getSalesInfo($client_id) it would be less painful to go re-write the SQL in that file, only, to have a MySQL version and a PostgreSQL version.

Data types
Take a look through some of PostgreSQL's available data types. It has types to efficiently store all sorts of amazingly useful values. By comparison, MySQL's available types are child's play.

DB abstraction class
I like Jeremy Zawodny's post called Database Abstraction Layers Must Die

The only problem I see with citing this as backup for your position is that, halfway through, he completely contradicts the rest of his post by saying:

I use a revolutionary new programming technique. Instead of littering my code with those calls, I put my core data access layer into a library

What else is a library except a layer of abstraction? Perhaps he doesn't make his abstraction as abstract as some others, but then he should write about that. His entire post about "database abstraction layers" is completely undermined by his lack of clarity. And I'm sorry to say, but so is yours.

DB abstraction class
I don't think it's a contradiction as much as it seems. What I'm not into is generic database abstraction that's meant to replace SQL (like DB_DataObject) - but what I am interested in is having all of my SQL in one place - for the optimizing reasons named above. (And "one place" might mean one directory of classes - not necessarily one file.)

DB abstraction class
Perl5 has always had an abstraction layer — the DBI module. In fact, almost noone even uses anything else than it.

That's because it takes the right approach to abstraction: it abstracts all the truly common things like running queries, fetching the results of a SELECT, connecting to a database with credentials etc. It does not try to abstract SQL or to offer any unified interface to particular features available outside the SQL scope (like the network settings of a particular database client). Rather, it just defines a standard interfaces for drivers to offer access to these features through.

Ideally, then, you stick all your database access code in a single module encapsulating your business logic (optimized as much as you want to). The rest of your code goes through this module to communicate with the database.

If and when you switch database engines, all you have to change is that module. Such a change cannot and will not be free, ever, but separation of concerns is a useful principle to reduce costs. This distribution of responsibilities outlined eases the maintenance cost for everyone involved — application maintainers can make changes to the business logic consistently and easily (while still getting as much out of the database as possible), driver maintainers need not concern themselves with offering a basic database access API, and the DBI maintainers need not try to cover every obscure features and settings all the different database engines expose.

DB abstraction class
I kind of agree with your disagreement. However, it is quite easy to use your procedural functions collectively as a "DAO". It's simply a layer of calls that go between the database and the rest of your application. The main difference, of course, is that you go in and manually make changes rather than switching out a different class.

Of course, DAOs do have their place. This is mostly in applications you plan to distribute to users. A DAO makes it easy for the end user to choose their own database server, and it makes the application more flexible as a whole. However, as you point out, for applications for specific use (rather than, say, a CMS you plan to distribute), a simple layer of calls is all that's necessary to make DB changes more painless.

Foreign keys
True, but the sloppy "catch as catch can" implementation leaves me cold. The fact that I can create two tables, one innodb the other myisam, and set up "fk" relationships that aren't there but generate no errors is one of many of the issues I have with MySQL.

If there was a version that errored out when it should on things like inserting a number that overflows its type, or fks that aren't really there, and forced only use of innodb (or at least made it default table type) I'd be more likely to use it more often, but for me, MySQL is a SQL interface to a storage subsystem designed for things like text storage et. al.

For accounting and other things that have to get it right or error out, almost any other open source database is a better choice.

If you like PostgreSQL, then definitely try firebirdSQL and a few other OS databases out there.