SQL Server 2000/2005 中棕地数据库开发的最佳方法
我最近接手了一个 SQL Server 2000 数据库的开发工作，需要一些帮助。 我们计划很快将其升级到 SQL Server 2005。 该数据库的表上没有审计字段（CreatedBy、CreatedDate 等），没有外键，总体设计很糟糕。 有六个程序使用内联 SQL 和其他旧的/不好的做法直接访问数据库。
我想清理架构和数据访问。 对于一个好的起点，您有什么建议吗？ 这是一个生产数据库，在改进的同时它必须继续工作。
I've recently taken over development on a SQL Server 2000 database that needs some help. We're planning on upgrading it to SQL Server 2005 soon. This database has no audit fields on the tables (CreatedBy, CreatedDate, etc.), no foreign keys, and terrible overall design. There are half a dozen programs that directly access the database using inline SQL, and other old/bad practices.
I would like to clean up the schema, and the data access. Do you have any suggestions for a good place to start? This is a production database, and it has to continue to work while it's being improved.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您可能必须从访问数据库的应用程序开始。 您很可能会发现对数据库架构的任何更改都会破坏其他应用程序。 我发现的最常见的罪魁祸首是 select * sql，然后根据列位置访问数据。 如果在最后一列之前插入一列，该代码将会中断。 此外，除非您对新列使用默认值，否则任何插入命令都将失败。
最好的办法是了解这些外部程序如何使用数据库，然后设计一个新数据库，然后一次将每个程序迁移到新数据库。
在生产过程中对此数据库进行更改几乎肯定会破坏其他应用程序。
You're probably going to have to start with the applications that access the database. More than likely you will find that any changes to the database schema will break those other applications. The most common culprit I've found is select * sql followed by accessing the data based on column position. If you insert a column before the last column, that code will break. Also, unless you use default values for your new columns, any insert commands will fail.
Your best bet is to understand how those external programs are using the database, then design a new database and then migrate each of those programs over to the new database one at a time.
Making a change to this database while it is in production is almost guaranteed to break the other applications.
您也许能够纠正、分析、规范化等模式，同时维护视图背后的当前模式/界面。
在视图上使用 before 触发器可以确保应用程序按预期写入和读取。
通过这种方式，您可以开始将客户端应用程序迁移到新架构，同时允许当前应用程序运行。 并且您的数据在新模式中更安全（DRI、FK、DF、CK 等）。
这也使接口合同对于每月运行一次的意外电子表格保持一致，并且没有人知道它对于月末报告至关重要......
You may be able to correct, analyse, normalise etc the schema while maintaining the current schema/interface behind views.
Using before trigger on the views can ensure apps write as well as read as they expect.
This way you can start to migrate the client apps onto new schema whilst allowing the current app to work. And your data is safer (DRI,FK,DF,CK etc) in it's new schema.
This also keeps the interface contract consistent for that unexpected spreadsheet that runs once a month and no-one knows about it is essential for that end-of-month report...