Intro

I have been corresponding with one of our readers who has been interested to learn about my recommendations for Prado vs. Code Igniter or other frameworks. I thought it might be interesting to the rest of you to hear my recommendations as well, as there seems to be very little material out there regarding Prado. Below are the relevant portions of my initial recommendation:

Initial Recommendation:

Code Igniter and Prado are very different approaches to frameworks, so it really depends on what you are looking for. Prado has an everything-and-the-kitchen-sink approach and use a Code Behind pattern from separating look and logic.

Code Igniter is very light-weight framework, but much more easily extended, and it uses the Model-View-Controller pattern to separate look and logic.

Prado has some built in internationalization support (http://www.pradosoft.com/demos/quickstart/?page=Advanced.I18N), but if you decide on Code Igniter, this may help: http://codeigniter.com/wiki/Category:Internationalization::Internationalization_Views_i18n/

As for web services, both have some built in support for the more popular services, and I’m sure that no matter which service you are looking for, someone has implemented it for Code Igniter.

Overall, I am personally a big fan of the Code Igniter framework, mostly because it is the one I have used the most. Prado (as you can see from my benchmarking post) has some performance issues and is significantly more difficult to learn. Code Igniter is also more easily expandable. However, I have had issues with Code Igniter on very large applications.

In summary, I would recommend Code Igniter only for simple or medium-difficulty applications. If you are creating a blogging system or very basic shopping cart, or even a web forum, I would say Code Igniter is the way to go to make your life easier. If you are building more complex applications that involve large amounts of form processing, more complicated calculations or if you are already familiar with some form of code behind (ASP .Net for example) then Prado would be more to your liking.

On a final note, if you are interested in something with much more complex and fine-grained permission management, I have found that CakePHP has the cleanest and most intuitive access control list implementations out of any framework and it is well integrated with the rest of the framework.

Response

At this point the reader pointed out some anomalies in my recommendation, so I had to rethink my position somewhat. The most glaring issue being that the Prado Benchmarks post I made shows major performance issues with Prado, so why would I recommend it for larger applications?

Second Recomendation

With Code Igniter, I found a lot of issues with mapping URI’s to controllers for more complicated URLs. This becomes a very big problem when you need to do advanced filtering. As I noted in my Fugitive Thought post on Pretty URLs, Code Igniter does not allow you to use both the nice URL mapping along with GETs, which means that if I want to filter by any more than 1 field, I have to either write a lot of extra code to figure out which parameters are provided, which are not and assign them to proper variables, or else I have to give up the pretty URLs in favor of everything being pure $_GET values and all pages going to index.php. For example, if I have an advanced search that I want people to be able to bookmark / link to with three different fields, a normal search file would have a URI like this:

http://myserver/blogs/search?a=blah&b=bloh&c=bleh

With Code Igniter, I can easily map /blogs/search to a specific controller, but I cannot use the $_GET values at the same time. What I have to do is generate something that will use this instead:

http://myserver/blogs/search/blah/bloh/bleh

This is all well and good, but what happens when each of these fields is optional? There are some obvious solutions, but they all require to implement extra code simply because the framework is saying “no”. I ran into a few other aggravating issues with Code Igniter as well that felt like they were limiting me far too much on large applications that need more complicated features, including session management and permission handling, etc.

Don’t get me wrong, Code Igniter does a LOT of things right, and it is very lightweight. If you put in the proper add-ins like the Smarty templating engine and make use of its template compilation and caching, you can do a lot of advanced stuff and keep it very fast. The reason I recommended Prado for larger applications despite the benchmarks is that a lot of the Prado stuff seems to scale fairly decently. The benchmarks I have displayed are ludicrously slow for a small application, but I don’t think it gets too much worse as you grow into larger applications because that overhead is a result of loading pretty much the entire framework in the beginning. I’m not positive, but I’m reasonably sure that the overhead can be mitigated with caching. And if the application is very large, then you are going to have some loading overhead anyway, and the very large suite of features that Prado handles will make up for a lot of the overhead in a lower development time and easier to debug code.

Code Igniter is nice for adding in just about any existing library because of the way they implement the “libraries” features; any existing class that does not rely on $_GET variables to the URL will pretty much just drop right in. This makes it wonderfully expandable, but also adds in a lot of redundancy when you need a lot of libraries since they are all disparate and not integrated and end up re-implementing a lot of the same features, whereas Prado has a lot of the features already built in and integrated to the framework so that all components can share a lot of features and code.

Akelos looks interesting, but you’re right about it being very young. One reason that both Prado and Code Igniter are high on my recommendation list is the amount of documentation. That really is priceless in making a framework usable. If you decide to go with something like Akelos, I recommend using an IDE with IntelliSense (http://en.wikipedia.org/wiki/IntelliSense) if you don’t already (Eclipse PDT is very nice for PHP intellisense! – http://www.eclipse.org/pdt/. NuSphere’s PHPed is also good, but it costs).

Introduction

I have been working on a number of projects recently that have to do with passing resources between a number of different steps. Each step has a method for determining who has permission to see the resource at that step, and who has permission to modify certain portions of the resource at that step. I am also applying for some jobs that have to do with this type of process flow control on much larger scales, so I thought that it might be a good time to formalize some of the patterns that I have found useful in implementing these kinds of systems.

This entry is going to focus largely on the database design needed to support this system. If there is interest, I can write future entries describing more of the application level design that utilizes these databases.

Problem Statement

The easiest way to describe these patterns is by the description of a practical use-case, so for the entry, we are going to design a basic ticket management system. In large enterprises, especially in the customer support departments, ticket management systems are very popular. The ticket management system provides some flow of control so that when a customer first contacts the department, a ticket is created and some information is attached to it, usually a problem description. If the first person that the customer talks to can resolve the issue on their own, then the ticket will probably only pass through that one step. Often, however, the information has to be passed to some other person, who will perform some action, add a note about it to the ticket, and then pass it back to some other person who will communicate the changes to the customer and repeat the feedback process until the issue has been resolved.

For our very basic ticket management system, we will assume five general entities:

1. Customer – a person who has a problem
2. Call Center – people who speak directly with the customer. Can solve basic issues like answering common questions.
3. IT – people who handle technical problems
4. Management – people who handle human problems
5. Auditors – people who can look at all tickets in the system so that they can review the processes

In our case, the resource is the ticket, we have a list of groups of people, and we have a good idea of who should be able to have access and when. The customer may be able to view the entirety of a ticket about them up to its current state. Auditors can view the entirety of any ticket. Call Center, IT and Management can view a ticket only when it is at a point where they would need to, and they can only add new additions to the ticket; they cannot modify or remove previous entries on the ticket. We will assume the figuring out what group a person belongs to is trivial.

The general database design for the ticket resource will be like this:

|---------------------|          |----------------------------|
| Tickets             |          | Notes                      |
|---------------------|          |----------------------------|
| id (int)            | <--|     | id (int)                   |
| customer (varchar)  |    |-----| ticket (int)               |
| create_date (date)  |          | author (varchar)           |
| problem (text)      |          | create_date (date)         |
|---------------------|          | message (text)             |
                                 |----------------------------|

The tickets are the main resource and is uniquely identified by its id (primary key). Each ticket will have zero or more notes attached to it, and each note can be uniquely identified by its own id. The note is associated with a ticket by the “ticket” field, which is a foreign key to Tickets.id. The original problem is stored in Tickets.problem, and any messages about the ticket are stored in Notes.message.

Status Pattern

One possible design solution I will call the Status Pattern. In this approach, we have a large table of all of the tickets in the system, and each ticket will have a “status” property. Whenever the ticket is passed to a new entity, the status is changed. In this case, we would add the “status” field to the Tickets table. The status fields can be any of the following values:

OPEN – ticket was just created, no notes attached yet

CALL CENTER – the problem has been stated and the call center is currently workign to solve it.

IT – the problem has been assigned to IT to fix.

MANAGEMENT – management has to do something to resolve the ticket.

CLOSED – ticket has been resolved in some way, and is no longer a concern

Note that there is only one entry for “CALL CENTER”. Once IT is done with the ticket, they can pass it back to the call center, it will show up on a call center person’s list, at which point they can look at it and most likely call the customer back to tell them that the issue is supposedly resolved.

Advantages

This system has the advantage of being very simple. There is a single place to look at all tickets, and in order to generate the list of which tickets a person can see, we just look for all tickets with a status belonging to that person’s group.

Another nice feature of the system is that it is easily expandable. If we have a new group that may need to view the tickets, all we have to do is add a new option for status values.

Disadvantages

While this system is quite simple, there can be problems with it. First of all, the database will end up cluttered with CLOSED tickets over time, which will decrease the access speed for tickets that are not yet resolved. This means that the database will get slower and slower as time goes on, which is not a good thing. Archiving old tickets is a matter of going through and finding all tickets with a CLOSED status, and storing them somewhere else, but this means that we no longer have the one central location to look at all tickets both current and passed.

If you are very security minded (perhaps these tickets contain incredibly sensetive information), then there is another problem with this design: we cannot easily separate permissions across ticket statuses. A lot of database security specialists will tell you that creating a different SQL account for each user with only the permissions that user can have are essential to keeping your information secure. This is discussed in more detail in the next section where we resolve this problem.

Queuing Pattern

This solution takes a lesson from queuing theory and is the type of approach used in areas that require more severe separation of concerns. Instead of having all of the tickets in one centralized table, we will create multiple tables. For example, we will have a table that holds all of the CALL CENTER tickets, a table that holds all of the IT tickets, a table that holds all of the MANAGEMENT tickets, and a table that holds all of the CLOSED tickets. Each of these tables can have the exact same set of fields, and will essentially be duplicates of the original Tickets table. Since the tickets and the notes go hand in hand, this actually requires duplication of both tables. As a basic example, we would have tables like this:

|---------------------|          |----------------------------|
| Call_Center_Tickets |          | Call_Center_Notes          |
|---------------------|          |----------------------------|
| id (int)            | <--|     | id (int)                   |
| customer (varchar)  |    |-----| ticket (int)               |
| create_date (date)  |          | author (varchar)           |
| problem (text)      |          | create_date (date)         |
|---------------------|          | message (text)             |
                                 |----------------------------|

|---------------------|          |----------------------------|
| IT_Tickets          |          | IT_Notes                   |
|---------------------|          |----------------------------|
| id (int)            | <--|     | id (int)                   |
| customer (varchar)  |    |-----| ticket (int)               |
| create_date (date)  |          | author (varchar)           |
| problem (text)      |          | create_date (date)         |
|---------------------|          | message (text)             |
                                 |----------------------------|

(etc.)

With this type of database schema, the operation for passing tickets from one group to another more closely resembles its real world function. When the call center wants to pass a ticket to the IT department, the data in the Call_Center_Tickets and Call_Center_Notes tables will be duplicated into the IT_Tickets and IT_Notes tables, and removed from the Call_Center tables. Essentially, we are physically moving the ticket.

Advantages

One advantage of this pattern is that it is easy to generate the most common reports. For example, when a Call Center employee views a list of all of the tickets they need to handle, it is simply a matter of selecting everything from the Call_Center_Tickets table, joined with the appropriate Call_Center_Notes. The only exception to this rule is for an Auditor. The Auditor will have to view all of the tickets, which may require a more complex UNION across multiple sets of tables. However, since there are generally very few Audit reports generated compared to the other groups, this is normally note a problem.

Secondly, this system will remain fast over time, since only the tickets that the users care about are stored in the table that they are accessing rather than cluttering up a single table with lots of CLOSED tickets.

Third, archival of old tickets is very easy, since all of them will be stored in the Closed_Tickets and Closed_Notes tables.

A fourth and very important advantage of this pattern is security. As mentioned in the Status Pattern, a very secure system should require individual permission levels inside of the database itself. If you do not understand this concept, read this decent guide to SQL injection, and pay close attention to the segregate users section. For this database schema, we would have a tickets_call_center user, a tickets_it user, and tickets_management user, etc. Each user will have onyl the permissions that they need to do their job. For example if a client logs in to look at their ticket, the system would connect to the database with the “tickets_client” user. The tickets_client user will only have read permission on tables, so that even if the user is able to find an application vulnerability in the system, they would not be able to change any of the tickets. A call center employee would be connected to the database using the tickets_call_center user, and would have INSERT and SELECT access to the call_center_tickets and call_center_notes tables so that they could create tickets and notes, and then DELETE access to the call_center_* tables and INSERT access to other tables so that they could pass the ticket on to other groups. In the worst case scenario, if they call center employee turns malicious and finds an application level vulnerability, they would only be able to affect call center tickets, and these they would only be able to delete. This helps to contain the damage they can do.

Disadvantages

However, this extra speed and security does not come without some sort of price paid in overhead. As mentioned before, the Auditor user will have to use more complicated queries to view all of the tickets together. This is not usually a major concern. Secondly, the system is significantly more complex. If it is coded properly with the correct use of transactions, there will not be any lost tickets when moving tickets between statuses, but this requires good design and a lot of testing to make sure it is correct. Also, there is a lot of duplicate types of information here that formal models for normalization may not take kindly to.

Pointer Pattern

As with any system, there are many possible solutions, and I cannot cover all posibilities, however this last pattern is another alternative to the previous two, and is in a way a hybrid of the two. With this pattern, we would store all of the tickets in a centralized pair of tables called simply “Tickets” and “Notes”. However, instead of having a status field to manage the permissions, we can have a set of individual tables for each type of user. One could be named “call_center”, and would simply be a list of all of the Tickets.id values that correspond to tickets that are currently assigned to the call center. This pattern would be duplicated by tables such as “it”, “management”, etc. This allows us to emulate the real world process again because we can pass a ticket from one group to the next by removing it from one list and adding it to another. However, this pattern does not provide any advantage over the previous two since we cannot secure permissions between entities, and we are required to do JOINS across tables for any sort of request. I am mentioning it only because I have seen it used.

Conclusion

Keep in mind that these example are simplified. In the real world, you will need to take into account other facts such as making sure that call center employees can still view older tickets in case the person calls back a long way down the line. Your choice of the pattern to use depends a lot on personal preference, on the abilities of your DBMS, and on the application where it is being used. For example, some DBMS’s may allow you to have more complex permission schemes, so that you can properly secure data in one table rather than having to separate it across multiples ones. When you take this into consideration, the Queuing pattern may not have as many advantages. Any professional application development requires some judgement on the part of the developer, and there is no silver bullet.