Posted by Jason Kester on 06/22/06 11:42

Ah, the familiar pain of first seeing a new client's existing
"database"...

I've been lucky that I usually have access to the source for whatever
application was touching the offending database. Generally, I'll
quietly make changes (add keys, relate tables, change varchar(255)'s
containing "Y" & "N" values to bits, etc.) and modify the backend code
to deal with it. Quietly is the key here, since you'll likely be
lopping off large amounts of "data" that is no longer hooked up to
anything due to the lack of foreign key constraints.

If you can isolate the applications that manipulate the data and get
them rewritten, you can implement the View technique described in
another post. When I've done this in the past, I've simply dropped
tables in the old database as I went along (after porting them of
course) and replaced them with views to the new database. At some
point, you'll find that most of the code is pointed at the new DB, and
you can rally support for a Last Big Push to move the rest over.

Good luck!

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/

---
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/



metaperl wrote:
> I work at a place which is currently running SQL 2000, but they are
> planning to migrate to 2k5. I was thinking that this is the perfect
> opportunity to fix all the weaknesses we have had in our data model for
> the longest: primary keys and foreign keys with different names, use of
> character columns for boolean fields, use of integer columns for
> toggles, no referential integrity, etc.
>
> So, even if I create my Utopian perfect data model and modify all of
> our data loaders to use it, our live website must use the old incorrect
> version because there is way too much work involved in redoing the
> code.
>
> My question then becomes: if I have a correct version, how easy and
> with what approach would one take the data in the correct one and
> mirror it to the poorly designed schema?

[Back to original message]