Re: Database migration and redesign

Since you're in the process of modifying a database, you could find this useful:

Agile Databases Techniques, by Scott Ambler.

Regards
Marco

On 9/11/06, Brandon Aiken <BAiken(at)winemantech(dot)com> wrote:
>
>
>
>
> 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.
>
>

--
Marco Bizzarri
http://notenotturne.blogspot.com/