Best Practices

Since I’ve hit the one-year anniversary of being a full-time freelancer, it seems like a fitting time to look back on this blog. Enjoy some of my favorite best practices posts:

Pricing Your Web Design Service
I wrote in this post that I prefer to give clients a fixed price per project, and that’s still true today. I did talk to a lawyer recently who preferred that I charge an hourly rate. That’s the world a lawyer lives in, so that isn’t too surprising. I’ve also worked a few times with a client who prefers me to give 3 different prices for best case, worst case, and expected case. I do that in my quotes, but so far the actual invoice has always been for the expected case.

Read the rest of this entry »

This certainly isn’t a comprehensive review of CakePHP. I have a client that wants to use CakePHP so they can support it internally. I’ve just barely scratched the surface, but the whole point of Cake is to allow you to develop something useful on a very short learning curve. Since it is freely available, I thought I’d share my first outing with this rapid development framework for anyone on the fence about giving it a shot.

Read the rest of this entry »

I almost didn’t write on this topic. There are a lot of people out there who know more about indexing than I do. The problem is all of the knowledge on the subject is being presented by and for database specialists. While their need is probably greater, I contend that web programmers who create dynamic sites need this information as well. So I’m going to share what I’ve learned to make my queries faster over the years. If there are any gaping holes of information, than hopefully those will get addressed in the comments.

An index is created on a particular table in your database. You specify one or more columns that you’d like included in the index, and the database then has a snapshot of what the table looks like when all the records are sorted on those columns. The idea is that the table will be able to return query results involving those columns more quickly. Here’s how it works.

Read the rest of this entry »

One of the most obvious vulnerabilities of web sites that interact with a database is the SQL injection attack. What’s that you ask? Suppose your site allows visitors to enter information that is used to generate a database query. A common example would be for the user to enter a password. A hacker will attempt to enter unexpected values that will cause unexpected SQL statements to be executed.

Read the rest of this entry »

In part 1 of this series I talked about turning your data audit trail into an interface feature. The primary purpose of an audit trail, though, is to allow you to see a clear picture of every change that has occurred with your data. While the history table solution that I offer is extremely easy to implement, it has the drawback of being contained inside your database. That means your data history is only as secure as your database.

Read the rest of this entry »

The post on this blog that gets the most search engine traffic is Leaving an Audit Trail In Your Database. I explain several techniques that allow you to store information in the database about how and when records are being changed, with the most comprehensive and effective solution being to create history tables. The obvious benefit is when data is inadvertently changed or deleted, you can track down what happened as well as restore the correct information. That’s not the only good use for an audit trail, though. Providing read only access of the audit information to users, gives accurate and up-to-date history reports of the data.

Read the rest of this entry »

Part of your role as a database architect is to save users from themselves. It’s inevitable that critical records will get modified or deleted from time to time. If the system has more than one user, the frequency increases exponentially. It’s tempting to take the approach that if users are going to be liberal about destroying sensitive data, that it isn’t your fault (true) and it isn’t your problem (not entirely true).

Of course, any system with critical data is being backed up, but backup systems were primarily designed to restore an entire lost database. Very few packages make it easy to restore isolated pieces of data. In many cases you have to roll the entire database back to a moment in time before the data in question was modified or deleted, losing all the changes that have occurred since. A better solution is to make it difficult for users to permanently remove or overwrite vital information. Build audit trails into your database.

Read the rest of this entry »

During the development of a project, you’ll repeatedly be faced with situations where there are multiple solutions to a problem. One choice is to create a robust answer that is reliable and will adapt well to the changing needs of the customer for the foreseeable future. Another option is a design that addresses the current needs of the system, but may not address variations that could appear down the road. This second option is frequently quicker to implement.

Read the rest of this entry »