JohnBs personal blog

I thought it’s been a while since i posted a proper code blog entry, so here’s one. It’s based on how i handle updating a database when multiple values for checkboxes have been posted, the same applies for multiple selections too from a drop down box.

First off, let’s simplify things. Imagine if you will, three tables, users, groups and users_to_groups. - pretty standard type stuff with the users_to_groups table allowing a many to many relationship between the users and the groups.

So when we want to assign a user to a group we add a new row to the users_to_groups table with the userid and groupid for each group we’re adding the user to. This is based on say, the following form submission;

Various code I’ve seen before handles this by taking the list of groupid’s and looping over it and doing an insert each for each item - usually having purged existing assignments beforehand, for example;

Now having worked for a large company with a team of DBA ninja’s - where possible we left the DB do as much processing as possible and this is one such case where our logic was wrapped up into a stored procedure.

So what do we have, a userID and a list of groupIDs that we want assigned to the user. I’m not going to look at the stored procedure, but my SQL is pretty simple;

I’m using a nested select statement in my insert statement. The nested select statement is selecting the userID that we passed into it and a column named item (aliased to groupid) into a sql (2000/2005) function udf_List2Table() - this as you’d expect, takes a list of values and converts it into a table on the fly. The code for the sql function can be found here - NOTE: I didn’t write this function.

I much prefer this method to looping over a list and performing an insert each time - keeping it all in SQL just seems so much cleaner. This logic can then be wrapped up inside a CFC or in this case a stored procedure which can then also handle the delete processing too before the inserts. Obviously, as is often the case implementations of DB functions vary across different servers but I’m pretty sure almost all the current servers have some form of custom functions so this behaviour can be duplicated there.

Another argument is that using RDBMS specific SQL, eg functions, stored procedures, views etc makes your app none portable between between the different DB servers - this usually only comes into play when you plan on distributing an application - but if it’s more performant to have the RDBMS handle this type of stuff then are these applications sacrificing performance for their own ease of maintenance of the underlying CF code?