开发人员常犯的十大数据库错误

翻译 2007年09月12日 09:40:00

Although fashions come and go in software development, some things stay remarkably constant. One of these is the use of databases. You may be wonderfully up-to-date with an AJAX Web interface or the latest whizbang Windows user interface, but under the covers, you're probably still pumping data in and out of a database, just as we all did a decade or more ago. That makes it all the more surprising that developers are still making the same database mistakes that date back to those good old days of Windows 95 and before. Perhaps it's just that most of us learn to use databases on the side, rather than really studying them. In any case, here are my nominations for the biggest mistakes that I see over and over again.

选择了错误的数据库

Not all databases are created equal — which means before you do anything with a database, you have to pick the appropriate database. Time and again I've seen Access databases groaning to bear the load of huge data sets that would have been child's play for SQL Server, or harried users trying to pay for and set up SQL Server to hold a few hundred rows of data. Broadly speaking, there are three tiers of databases in the market these days: desktop and embedded databases suitable for smaller tasks, "Express" versions of the major players that are good up to a few gigabytes of data, and the truly enterprise databases like SQL Server, Oracle, and DB2 that can handle just about anything you can throw at them. Before you do anything else, you need to make some realistic estimates about the amount of data that you'll be storing and pick the appropriate product to do the storage.

选择了过多的数据库

APIs such as ODBC, JDBC, and OLE DB have promoted the notion of database independence - the idea that you can write your application code in such a manner that you can plug any database at all in for data storage. Well, yes, but there are compromises. I've seen a lot of teams go down the rat hole of database independence, writing layers to translate all of their SQL statements to some lowest common denominator dialect that every conceivable database will support, and at the same time giving up on advanced features available in any particular database. The notion seems to be that some client in the future might want to switch to Oracle or DB2 or FoxPro or whatever, so it's best to be prepared now. On the contrary: when you're starting out with a new product, pick your storage engine and write to it. If your product is good, people will install the database you specify, and you won't be wasting untold man-hours supporting "just in case" scenarios that you'll probably never need.

了解你的数据

If I had a dollar for every time it turned out that some customer numbers had seven digits instead of six, or that the registrar's office really did allow students to register without a social security number due to privacy concerns and so the column has to be made nullable - well, I'd have a lot of dollars. Database design can't be done in a vacuum, away from the business rules. It's critical that you get the input of the actual users of the data, and hammer on them to find out for sure how big each column needs to be, what rules apply to it, what types of data it will hold, who can update it, and so on. Otherwise, you're setting yourself up for costly rework down the line. You'll learn to dread sentences starting with "Well, it looks fine, except..."

它就像Excel一样,不是吗?

现在有一种趋势,人们似乎都想当然地认为任何一个开发人员都懂得如何去建立一个数据库,这一趋势在小的软件开发公司中为甚.坦白地说,这令我十分困惑.你可能不会想当然地认为每一个程序员都懂得如何用C#写程序或者部署一个Web Service,那么为什么却想当然地认为每个程序员都是数据库方面的专家呢?结果是那些甚至连范式和数据库数据类型都没听说过的开发人员创建和设计了不计其数的数据库,我多次看到众多地信息被存放在一个很大的表中而引起的更新异常和性能方面的低下.如果不幸的是你也处在这种状况下,而且你也有自知之明的话, 那么你最好现在就去参加数据库方面的培训.高效的数据库设计是需要系统地学习的,而不是通过试验和错误中得来的!

第三范式不是完美的

与上一错误相对的是,片面地对数据库的了解也是一件非常危险的事情.我曾看到过一个称为"数据库方面的专家"的开发人员规范过的数据库,他坚持把所有信息都放到表中.记忆尤其深刻的是他连"yes"和"no"这样的信息都存放在tblAnswers表中以通过AnswerID外键在其他表中引用.没错,你应该知道规范化的知识,但是你也应该培养什么时候停止规范化和反规范化以提高性能等等一些开发技巧.

What a Great Place to Hide Application Logic!

Stored procedures and triggers are a wonderful thing. When you've got multiple clients accessing a database, they can be a great way to make sure consistent data processing takes place. But they can also turn into an ugly black box in which application logic hides, unknown to Web and thick client developers, generally unseen and unreviewed. Too often database code isn't subject to the same standards of design, test, and code review that we demand for the rest of our applications. When you're tempted to put code in the database, take a moment to ask yourself whether it really belongs there.

谁需要备份?

Who needs backups? You do. Presumably you're storing data in a database because it's important enough to hang on to. Somehow, though, I end up walking into situations where "nobody got around to it" on a regular basis, and valuable data is lost forever because hardware, hackers, or just plain mistakes munged the database and there wasn't any backup. Your backup plan (including things like frequency, type of backup, and how often you're going to take backups off-site) needs to be in place at the start of the development cycle, not at the end.

没错,你需要版本控制!

Speaking of backups, you need to worry about schema changes to your database as well as data changes, and you need to keep track of those schema changes in such a way that you can recreate the database at any point in time. That's right, if you want to do a really professional job of building software you need to extend version control to your database design. It doesn't do much good to be able to recover version 0.784.5 of the software to test out a customer bug if you can't also produce the corresponding database. If your database developers are cheerfully writing stored procedures and tweaking table designs without leaving any trace of their work, you've got a problem.

使用这些工具

Modern databases offer a lot more than just a series of buckets that you can toss your data into. They also come with a substantial variety of tools to make it easier to manage that data. For example, SQL Server makes it easy to inspect the plan of attack that the server is using for your queries, and even includes wizards to tell you what indexes would make your queries more efficient for the actual load you've been throwing at your server. I've had great success running these tools on client databases and speeding things up, or lowering CPU usage by a factor of two - but the fact is, they shouldn't have had to call in a consultant to tell them to use the stuff in the box. If you don't know what tools and utilities come with your database, and what they can do for you, then you're paying for value that you're not receiving.

Don't Assume Everything is a Nail Just Because You Have a Really Big Hammer

Databases have a tendency to take over all data storage for an application. I've seen applications that tried to build an entire metadata-driven user interface, and then stored that metadata along with user preferences in the same database that was holding the business data. This is a good way to complicate your life and kill performance; some data really does belong in local files, not in a client-server database across the network. When you're storing data, you need to evaluate the different places you can put it (database, registry, plain text files, XML files...) and pick the appropriate spot for each piece of data. Don't just automatically shove it into a database just because you have a connection string handy. These days, there's probably more of a tendency to overuse XML files than relational databases, but the principle still holds.

关于作者

Mike Gunderloy is the author of over 20 books and numerous articles on development topics, and the Senior Technology Partner for Adaptive Strategy, a Washington State consulting firm. When he's not writing code, Mike putters in the garden on his farm in eastern Washington state. 

文章出处:http://www.developer.com/db/article.php/10920_3589351_1

.Net开发人员常犯的6大安全错误

业内分析人士估计,有超过70%的安全漏洞是在应用程序中被发现的,大部分都是由代码内存在的安全缺陷引起的。微软已经为.Net环境添加了大量的功能,帮助开发人员创建安全的应用程序,例如,身份验证已经成为开...

AngularJS开发人员最常犯的10个错误

简介 AngularJS是目前最为活跃的Javascript框架之一,AngularJS的目标之一是简化开发过程,这使得AngularJS非常善于构建小型app原型,但AngularJS对于全功...

Java(Android)开发人员最常犯的10个错误

一、把数组转成 ArrayList 为了将数组转换为ArrayList,开发者经常会这样做: List list = Arrays.asList(arr); 使用 Arrays.asList(...

AngularJS开发人员最常犯的10个错误

AngularJS是目前最为活跃的Javascript框架之一,AngularJS的目标之一是简化开发过程,这使得AngularJS非常善于构建小型app原型,但AngularJS对于全功能的客户端应...

Java开发人员最常犯的10个错误

一、把数组转成ArrayList 为了将数组转换为ArrayList,开发者经常会这样做: List list = Arrays.asList(arr);使用Arrays.asList()方法可以得到...

数据库开发者常犯的十大错误

数据库开发者常犯的十大错误 尽管软件发展中的热点技术层出不穷,不断地变化,有一些东西却一直未曾改变,其中之一就是开发人员对数据库的使用和设计开发。你可能会兴奋地紧跟时尚创建一个AJAX Web界面,或...

Android开发人员常犯错误集

TextView(往往 TextView 派生子类同样适用)调用 setText 方法设置一个 int 型的数据,千万要将该值转为 String,否则在某些设备中它会默认去查询 R 文件中定义的资源,...

(转)开发者常犯的十大加密错误

原文:Top 10 Developer Crypto Mistakes  作者:crazycontini  翻译:Kenneth 在经历过成千上百行的代码审计以及在Stack Overflo...

.NET开发人员犯的6大安全错误

  • 2010年03月05日 10:05
  • 11KB
  • 下载

感知计算:开发人员的十大资源

很长时间以来,感知计算是最令人兴奋的技术之一,而且这种技术的确刚刚兴起。什么是感知计算?简单来说,它是一种人机交互的全新方式。不再只是与标准键盘和鼠标输入控制器相关;它涉及到触摸、手势、声音和语音识别...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:开发人员常犯的十大数据库错误
举报原因:
原因补充:

(最多只允许输入30个字)