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