By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,139 Members | 1,897 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,139 IT Pros & Developers. It's quick & easy.

SQL 2005: creating "correct" and "incorrect" versions of a database

P: n/a
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?

Jun 20 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
metaperl (me******@gmail.com) writes:
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?


I'm not really sure that I see the point with redesigning the
data model, but then replicate the data to the old crappy version.
Or is the web site, only one consumer of the database? What if
updates are performed from the web site?

What you could do is to redo the data model, but then define views
that mimicks the old tables. But again, this would be useful if you
also start with a long-term conversion of the web site.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 20 '06 #2

P: n/a
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?


Jun 22 '06 #3

P: n/a

Erland Sommarskog wrote:
metaperl (me******@gmail.com) writes:

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?


I'm not really sure that I see the point with redesigning the
data model, but then replicate the data to the old crappy version.


Because all of the ASP code is based on the wrong version of the
database.
Or is the web site, only one consumer of the database? What if
updates are performed from the web site?
Good point. That part of the website should definitely be rewritten to
send things to the correct database model.

What you could do is to redo the data model, but then define views
that mimicks the old tables.


Yes, this is what I concluded a day or two after posting this.

Jun 24 '06 #4

P: n/a
metaperl (me******@gmail.com) writes:
Erland Sommarskog wrote:
I'm not really sure that I see the point with redesigning the
data model, but then replicate the data to the old crappy version.


Because all of the ASP code is based on the wrong version of the
database.


Yeah, I understood that part. What I question is whether it's worth
the pain to add extra complexity to the system by adding an another
data model.

Using views etc may be good as a transitory measure, but I think that
such a move is only defensible if the long-term is to rewrite it all.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 24 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.