Database migration and redesign

I've been tasked with the unenviable job or migrating a MySQL 4.0
database to something more usable (namely, PostgreSQL 8). MySQL 4.0
doesn't even support basic things like subqueries, and in order to
emulate the effects that RULEs, TRIGGERs and VIEWs bring, they had been
using PHP scripts to replicate and build tables across the database (not
from one server to another - within the DB itself). The database was
built across 5 separate schemata simply to organize the 50 odd tables,
and all the tables are using the MyISAM engine which means no
transactions, no row-level locking, and no foreign key constraints.

Yeah. It's ugly. You should see the front-end.

My question relates to primary keys. The vast majority of tables have a
primary key on a single char or varchar field. Is it considered better
practice to create a serial type id key to use as the primary key for
the table, and then create a unique index on the char and varchar
fields? Should foreign keys reference the new primary id or the old
unique key? What about compound [primary] keys?

Also, any suggestions for good DB design books would be appreciated. I
no longer have any good DB design books, and I don't know what's good
anymore.

--

Brandon Aiken

CS/IT Systems Engineer

Confidentiality Notice

This email, including attachments, may include confidential and/or
proprietary information, and may be used only by the person or entity to
which it is addressed. If the reader of this email is not the intended
recipient or his/her authorized agent, the reader is hereby notified
that any dissemination, distribution or copying of this email is
prohibited. If you have received this email in error, please notify the
sender by replying to this message and deleting this email immediately.