I'm leading two data layer related sessions coming up, one for Cloudstock — Developing an Enterprise-ready Database for the Cloud — and the other for Dreamforce 10 — Introduction to the Data Model. Reading around a bit as part of my preparation, I found this great piece on StackOverflow: Database Development Mistakes Made by Application Developers. As I read through it I couldn't help but notice that the Force.com platform addresses many of these issues right out of the box, without any extra effort.
Before we take a look, let me point out another of my recent posts: First, Free the Developers. It's my view that the more a platform handles for developers, the better the solutions they produce will be. Why? Because they don't have to deal with the basics — they won't have to develop, test, fix or maintain core services. They can concentrate on innovation. Maybe it even helps them build a bit of the old cognitive surplus.
Jumping in (and not in any particular order):
1. Not using appropriate indexes with Foreign Keys. On the Force.com Platform, Foreign Keys are handled via a lookup relationship. As soon as you create the lookup relationship, the platform has done the rest. You never need to worry about it. 2. Not enforcing referential integrity. Like the question of indexes, the Force.com Platform automatically ensures that a lookup field contains only relationship appropriate data. Also, setting a field to require a value is as simple as checking a box on either the object or, if you want conditional requirement based on the user, checking a box on the page layout.
3. Using natural rather than surrogate (technical) primary keys. This is an interesting argument that developers tend to have. It basically boils down to a question of table purity — does a field that's there just for Row Identity add any value? Yes. Absolutely. And the Force.com Platform gives you this on every row, for every object, out of the box. As an added bonus, these ID's are actually GUID's — and the G is truly global. It's both object type and org aware. Very convenient.
4. Selfish database design and usage. The concept here is that developers only think about their own needs — database as persistence store for their app — when in fact the database needs to be used by other functions. The contributor writes: "Reporting and MIS tend to be poor cousins in application design and only done as an afterthought." Force.com includes analytics and integration right out of the box. No need for significant special consideration in most cases. Your data layer is automatically compatible with these other constituents.
5. Failing to test for scalability. One of my favorites! The Force.com Platform is currently managing more than 20 billion records. Most of these are in custom objects — think tables — designed, built and operated by customers, often by their Button Click Admin. Don't get me wrong: it's possible to make a bad design, but it's generally easier not to. So people usually don't.
There are a lot more items in that discussion that we could talk about — the hazards of over normalizing and over denormalizing, the perils of using the wrong SQL, not sanitizing input (if you want more on that, check through the blog — there's a ton of security info — handled for you automatically about 90% of the time). But this is a good start.
I'm looking forward to these sessions. Cloudstock is new but should be ridiculously cool. Dreamforce is always a riot. I'm guessing my voice will give out around Wednesday AM. Good thing my sessions are Monday and Tuesday. See you there!