Over the last 6 months I have been working on Ditto Music’s new “Record Label In A Box” system, this is a new way to create your own record label with the added benefit of receiving help, support and all the tools needed to run a successful label.

Part of this system is the ability to create legally binding contracts for your artists, initially we offered 6 contracts but this is always subject to change and the data for each contract type can change dramatically.

Initially it’d be tempting to create a model for each contract type, i.e. ContractMerchandise.php, ContractRight.php – this is a little messy and not very DRY. Instead I set up the system so that we had 3 models rather than 8. Here is a sample case ERD for which I will show you what and how I did it.

In the above image you can see we’re allowing our users to create their own school records, these records could be one of 4 types (If you notice the INSERTS for school_types, we specify the table as well as the name). In the example I have specified custom_field_x for our different schools but these could be anything depending on what you need.

Setting up the Primary Keys

To begin I personally like to store the important primary keys in my own custom configuration file, people have different methods of doing this, you can check my post here about storing primary keys.

 

Setting up the School Model

Next let’s set up the main School model method, here we just need to specify our relationships, you can feel free to add any methods you need from your SchoolsController if you were actually building this but for now we’ll leave it blank.

 

Setting up the SchoolType Model

Next we’ll set up the SchoolType model, in this model I will do a few things, firstly I will set up a few methods to be able to find the SchoolType by id, as a full list or simple list. As well as this I will also introduce caching to save querying the data each time.

 

Setting up the School Cache Config

In order for the caching to work in the above example, we’ll need to add the school Cache config to the bottom of the bootstrap.php configuration file.

 

SchoolDetail – Initial Model

This is the core of what we’re doing, this is the model which will select the appropriate table and bind it all together, for this reason I’ll build it step by step so you get an idea of what is going on.

So far so good, we’re telling the model not to use a table, this is because school_details will not exist. We’re specifying $this->schoolTypeId which is very important for selecting the appropriate table.

SchoolDetail – Finding and Updating the appropriate table

We will be using a custom protected method _defineTable to specify the appropriate table, this will be referenced from various model callbacks so it’ll be automated – the only thing you must ensure is that you specify $schoolTypeId from the controller or model depending on what you do.

The above method references findByIdthis is a method in my AppModel file which will get all the fields by primary key – feel free to change this, initially we just want to retrieve the ‘table’ field. We use setSource to tell CakePHP which table we’re using.

Next we will tell the model to call $this->_defineTable(); before any find query as long as we have specified $this->schoolTypeId.

We do the same with beforeSave, only this time we’re specifying the create date if it’s an insert only and the modified is always being specified. There seems to be an issue with setSource which stops this from being automatic.

SchoolDetail – Setting up Validation Rules

It’s likely you’ll want to specify validation rules, but as each table may have different fields, you’ll want to be able to fetch the appropriate validation rules. Here is what we’ll do.

If we use beforeValidate we can check there is a schoolTypeId specified, and if so change the value of $this->validate using our new protected _fetchValidationRules method (see below).

The above method we’re referencing the primary keys we stored in our config file earlier, the validation rules are all the same here for demonstration purposes but you can change that however you like. This may be a little large, but it could be moved elsewhere if need be.

Note: checkAgainstRecord is a custom validation rule you can find in this post

SchoolType – Saving and Updating Records

Finally we’ll need a method to save or update records which you can easily reference from a controller.

This should be fairly self explanatory, we find the SchoolDetail record via schoolId, if we find it we update the record otherwise we create a new record.

Specifying the School Type Id via SchoolsController

The last thing we need to do is actually specify some methods in which you can easily set the schoolTypeId for the model from within your controller. The first method is one you can use when you know the schoolTypeId foreign key. (flashRedirect is a combination of setFlash and redirect I have in the AppController)

The final method will be one we can reference when we don’t know the schoolTypeId but we do know the schoolId.

 

And that’s it really! Put it all together and you should be able to find, save and update to the individual school tables without creating a model for each school type table. You can add or remove tables records and not have to do much duplicate code by creating a new model.

You may use $this->render() from your controller to specify various views or elements if needed.