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.
--
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.
