Many of us consider form validation to be a server-side language effort, with a sprinkle of front-end JavaScript to enforce the simpler rules. It can be easily forgotten though, that the database is a great tool for validating data.
There's a lot of code out there (I'm guilty of this myself) that "works around" the database's innate ability to validate information inserted into it's tables. This validation comes in the form of constraints. You can define constraints on data, such as primary & foreign keys, the data type of the field or uniqueness.
Two-query style validation (not recommended)
When I talk about "working around" the database's ability to validate data, I'm primarily referring to this practice:
SELECT id FROM wishlist WHERE user_id = 2 AND product_id = 3 <cfif id.recordCount eq 0> <!--- insert wishlist item ---> <cfelse> <cfoutput>This item is already in your wishlist.</cfoutput> </cfif>
In the ColdFusion example above, we actually perform a query first, to determine if it's okay to insert data into the database.
Modelling your database table to support validation
To leverage the power of database validation, we need to make sure we model our database tables properly. The most obvious technique is to use a primary key or unique constraint on a single field. For instance, if we needed to ensure that the same email address can not be used twice to create a new user account, we might place a unique constraint on the email field in our users table.
If we wanted to create a wish list feature for our product store, we'd create a "cross reference" table, which links a record in the users table with a record in the products table. But, we wouldn't want to add the same product to a user's wish list twice.
We could construct the cross reference table as follows:
CREATE TABLE wishlist (
user_id int(11) NOT NULL,
product_id int(11) NOT NULL,
created datetime NOT NULL,
PRIMARY KEY (user_id, product_id)
);Constructing a table in this fashion forces uniqueness among the user_id and product_id fields. If an insert operation attempts the same combination of user_id and product_id already found in the table, the unique constraint on the primary key will be broken and a duplicate entry error will be thrown.
Leaving validation to the database
Lucky for us, databases communicate constraint related errors to the calling application fairly well. In the case of our primary key constraint, if we attempt to insert a record that breaks the unique constraint on a primary key, the insert operation will fail and an error will be returned to the application.
MySQL returns error code #1062, which means that we've attempted to insert a duplicate entry.
Sticking with ColdFusion to demonstrate:
<cftry>
<cfquery>
INSERT INTO wishlist (
user_id,
product_id
created
)
VALUES (
<cfqueryparam value="#the_user_id#">,
<cfqueryparam value="#the_product_id#>,
now()
)
</cfquery>
<cfcatch>
<cfif cfcatch.NativeErrorCode eq 1062>
<cfoutput>This item is already in your wishlist.</cfoutput>
</cfif>
</cfcatch>
</cftry>
We now have a script that "validates" our data in just one query. An unquestionable performance boost from the two-query validation script we started with.