Gimme.eu is one of the projects I’m currently working on. Gimme is a social networking platform aimed at (leisure-)clubs, school and local shops. It’s goal is to centralize all communications of a family and provide a local advertising platform.

Gimme.eu is build with the Code Igniter PHP framework. The application follows the MVC design pattern and uses a MySQL database (alongside intensive caching with memcached).

People familiar with Gimme will know that its main communication tool are stories. Stories are build with different types of content and then published to the followers of the organization. Off course stories are represented in the database with a table and many other tables to map there relations to other objects.

Recently we introduced a new concept in Gimme: concepts. Concepts are stories that are saved for later use. These concept stories are not to be viewed and handled publicly. When we looked at our application design we had some requirements:

• Concepts are stories, so they should live in the same table to avoid duplicity.
• All code is written for stories. We want to change the codes as little as possible
• Safety first! Concept should not be accessible by design. We don?t want to rely on coded if-statements or other checks to verify wether we are dealing with a story or a concept.

Redesigning the database

The stories table was renamed to a general table _stories. _stories is meant to contain both stories and concepts. They are distinguished by a new column ‘concept’ (default value false). The old stories table was replaced with ?view?. A view is basically a stored query that mimics a real table. The query that we?ll use to create the stories view simply selects all records in _stories that are not concepts:

In the same way we create a view to represent the concepts table:

Creating these views provides many surprising benefits:

• We can insert records into the real, general table _stories, but also into the views.
• Simply by changing the value of the concept binary column we can move a record from the stories view to the concepts view or visa versa

So far, no changes to the code have been made and everything is still working like before. The stories view acts just the same as the old stories table. The code is not aware of the changes made to the database.

To bring the concepts into our app we created a new controller and model specific for concepts. These operate on the concepts view and thus cannot mess with the original stories.