Save Table Relationship Data

DataMgr offers an easy way to save data describing many-to-many relationships. This normally tedious process is made easy with this free component.

Normally when you save relationship data, you have to add new records in a relationship table and remove records in the same table for relationships that no longer exist. In many cases, you don't want to delete and re-add records because of extra data that might also be stored in that table. Often this can involve writing tedious code with a lot of looping.

Fortunately, DataMgr can take care of all of that for you.

Let's see an example.

Suppose I have an "Admins" table (holding administrators for our site), a "Permissions" table (holding possible permissions for administrators) and an "Admins2Permissions" table (indicating which administrators have which permissions).

Let's say that "Admins2Permissions" has two fields "AdminID" and "PermissionID" referencing the primary key fields of the Admins table and the Permissions table, respectively.

Let's also say that we have a variable called "permissionslist", which holds a list of permissions for an admin (specifically, it holds a list of the primary key values from the Permissions table). This could be passed in from a checkbox field, for example. The primary key value for the admin is stored in a variable called "admin".

To save the relationship, we would use this code (assuming that you have the DataMgr component stored in variables.DataMgr):
<cfset variables.DataMgr.saveRelationList("Admins2Permissions","AdminID",admin,"PermissionID",permissionslist)>

Using that code, DataMgr will remove any records for that admin that are not included in the list and add any records that are in the list and not in the Admins2Permissions table for that admin.

The saveRelationList method takes five (5) arguments:

1. tablename: The name of the table holding the relationships.
2. keyfield: The name of the field holding the key value for the relationships.
3. keyvalue: The value of out primary key.
4. multifield: The field holding the "many" side of this relationship.
5. multilist: The list of values to be stored for the multifield as associated with the keyvalue in this table.

In order to instantiate DataMgr for use as in the example above, you need only initialize it with the datasouce. For example, if you wanted to initialize it to use a datasource of "MyDSN" (pointing to a MS SQL database), you would do this:
<cfset variables.DataMgr = CreateObject("component","com.sebtools.DataMgr_MSSQL").init("MyDSN")>

For a MySQL database, you would use this:
<cfset variables.DataMgr = CreateObject("component","com.sebtools.DataMgr_MySQL").init("MyDSN")>

You can also use Access (DataMgr_Access) or PostGreSQL (DataMgr_PostGreSQL). It should be easy to add support for other databases as well.

You can download DataMgr for free.


Version Note:
As of today (March 7, 2006), DataMgr is version 1.0.1. Unfortunately, support for MS Access prior to ColdFusion MX 7 still hasn't been fixed. I have, however, adjusted the error message to accurately reflect that. All errors thrown by DataMgr are also now of type "DataMgr". Additionally, a few small bug fixes have been made.