Building an Object Oriented DB with MySQL and PHP

Introduction

1. A database implements a physical data model which, to be used, requires the use of a "low level" query language like SQL. This language has, as advantages, a relative simplicity of implementation and of a great flexibility but is completely linked to the physical layer. The problem lies in the fact that a good application design should handle concepts or objects, meaning logical entities and not physical ones. It is what the design principle of splitting the logic and the data introduces partly and it is what the object oriented programming languages are trying to facilitate, but the problem is not completely solved by those.
2. An object or a logical concept can consist of several data resulting from several tables and databases. The implementation of such objects is complex just like their maintenance if all is handled starting from the physical layer of the data.
   I encounter this problem every day in my work: our "customer" concept relies on data scattered in various databases of various systems which are kept independent because all having different functionalities and technical capabilities. The consequence: impossible to handle the whole "customer" data in a consistent way, installation of synchronization processes, copies, data dispatching as well as multiple interfaces between the applications. It is an observation often repeated in the world of the CRM (Customer Relationship Management), the "customer" concept does not exist from a technological point of view in most companies. This fact remains valid for a large number of other various concepts.

These problems are all the more present when developing Web Applications, all the more with PHP and MySQL, PHP offering a still limited object layer and MySQL being a database mainly focusing on the "physical" storage of data. I do not say that this last point is a problem, on the contrary, it is undoubtedly the main reasons why the MySQL ease of implementation and performances are above average but it certainly implies some limitations. That's why, in the next pages of this article, we will see how to build a logical or object layer with a MySQL database and PHP. Our final objective is to simplify the development of web applications.

Principles & Design

Principles

Here are the principles of eDObject, the solution implemented by eDreamers to implement an object layer with PHP and MySQL:

• eDObject is based on a logical layer of objects, itself consisting of a physical layer and a logical layer;
• The physical layer provides the structure of the object and is directly described in the database using tables and fields;
• The logical layer implements the eDObject generic concept and provides "ready-to-use" components (Object Create, Object Edit, Object Delete, Form Builder, Table Builder, Report Builder etc) within a framework or toolbox (eDToolbox in this case);
• Each object consists of a master table and of one or more child tables;
• One to several keys binds the master table with each of its child tables;
• Constraints can be defined on top of each keys to further define the master/child tables relationship;
• To each object can be attached a workgroup composed of people who supervises, uses, monitore, etc this object.

Design - Database

The eDObject logical abstraction layer is based on the creation of new tables and fields. After several attempts of the various possible implementations, I chose to integrate these new tables directly in the database that contains the objects' master table. This approach facilitated the first implementation of eDObject.
A more advanced design would use a new database dedicated to the objects and would then make possible to not impact the targetted databases original data model. This design will probably be supported by the next versions of eDObject which will propose to choose between these two solutions.
Here is the data model of eDObject:

• xedobject is the main table providing the name of the object and of the master database and table of this object. Only the description field is optional but should be anyway filled in as it might be providing very useful information to the db administrator and th eusers (if displayed by the application).
• xedkey is the table defining the object keys which link the master and child tables. The keys can be compared to Foreign Keys but extend this concept to any field of the child table and not only her Primary Key.
• xedconstraints si the table defining the constraints to apply to the keys of the object.

Etape 1: Startup

This section describes the implementation of eDObject in an environment composed of a Apache webserver, a MySQL database and PHP. On MS Windows®, you can use tools like EasyPHP to create and setup this environment in just a few mouse clicks. eDObject is not binded to this environment and the principles & ideas described here can be applied using many differetn technologies.
You will also need a MySQL database administration tool. I recommend PHPMyADMIN, written in PHP, free and the most complete of all.

The database

Let's take the example of an employee database for a Human Resources department. The data model has been voluntary simplified to start with: The employee table contains the first and last name of the employee as well as two foreign keys, one linking to his function which name is contained in the function table, and the other to his team which name is contained in the team table.

As explained earlier, the creation of the eDObject layer involves the creation of 3 tables to add to the HR database: xedobject, xedkey and xedconstraints.

Create a first object

We'll now create a first 'employee' object by going through the following steps:

1. Create a new record in the xedobject table: The parent table is the employee table from the hr database. This object will be named "Employee" adding the following description "An employee object". The id field is left empty if you have selected the property "auto_increment" when creating the table (done if you loaded the SQL script from the eDToolbox package). If not, pick an id that is greater than zero and not already existing (1 should be ok if you haven't created any object yet). Keep this primary key in mind as we'll use it in the next steps.
2. Create keys linking the child tables with the parent one in the xedkeys table: According to our model, an employee has got a function in a team, consequently we're creating objects keys linking the employee table and the function and team tables. In the definition of an object according to eDObject the keys are not only linking the primary keys of the child tables but any filed of that table. We, therefore, are respectively linking the fnname and tmname fields of the child tables to the eefunctionid and eeteamid fields of the Employee object (objectid = 1).
3. Define constraints on keys in the xedconstraint table: to start with, no constraint will be defined.

The Employee object is now created with the following attributes: a first and last name, a function and a team.

While I am writing this article, I'm hearing questions here and there on the added value of creating such an object. Wouldn't it be easier to create an employee table, replacing the eeteamid and eefunctionid fields by eeteamname and eefunctionname and then only have one table? Why not, but how to you now manage your list of functions or teams independently? Impossible! This is why normalising your database is an absolute must from a design point of view and if you want to keep a total flexibility and allow your model to improve to answer new needs and ideas that will for sure pop up in your mind and the one of your users. You'll find some interesting articles about normalisation on the Developer SHED website.

Step 1 is now completed. In the next section, we'll deal with the application layer that will allow us to manipulate objects using the Employee object as example.

Step 2: The code - eDObject

The principles

Yes, any developper should take the time to think before going on with coding. Our objective is to create an application layer that will allow us to manipulate eDObject instances independently from their structure and content. The Employee object will be used to illustrate this principle.
This application layer can be split in two sub-layers: a first layer for storing the object concept and for making it available to a second layer that displays of the object.
The display layer gathers the elementary components for creating of more complex applications:

• an HTML form builder for the creation and the editing of object instances, eDObjectFormBuilder;
• an HTML table builder for the listing of object instances, eDObjectTableBuilder;
• a viewer to view object instance, eDObjectViewer.

These components lie on a structural sub-layer that contains eDObject, the heart of the solution modeling the object and allowing to make an abstraction of the database, and eDObjectSave that manages the creation and the update to the content of an object instance.

Implementation

There are three concepts in PHP that need to be perfectly understood before starting the implementation of eDObject: the object oriented principles, the associative arrays and the variable variables. Please read the PHP documentation if you need more informations.
Inside eDObject you can distinguish between the object structure and its content. The structure is generic and can be considered as the skeleton of the object, composed of database, tables and fields. The content is specific to each instance of this object e.g. employee 1.
Obviously, eDObject is a class from a programming point of view, using 5 types of functions:

• A constructor as for every object;
• Some "Builders", functions that build the object elements, mainly the eDObject structure;
• Some "Runners", functions that execute some mechanisms inside the object;
• Some "Getters", functions that enable access to the object properties and variables;
• Some "Investigators", functions that test some properties of the object.

These types of functions are not specific to eDObject, you'll usually found them in every class that has been written correctly, whatever is the object oriented language that has been used (C++, PHP, JAVA, etc.). My advice is to include the function type in the name of the function itself, for example getObjectKeys() for a 'getters' type and buildQuery() for a 'builders' one. You'll ease your life while writing, extending or maintaining your code.

Instead of detailing 350 lines of code here, I will describe the main principles followed during the construction of eDObject.

The eDObject class does not use any SQL code

Rather, it uses the services of an abstraction layer built by the eDMySQLMS and eDQuery components. eDMySQLMS is a class that retrieves the full structure of a database (db, tables, fields with their properties) and makes it available in easily-accessible associative arrays, whereas eDQuery eases the building, execution and the use of the results of an SQL query. In summary, eDMySQLMS allows eDObject to build the structure of the object and eDQuery provides the means to explore its content.

The eDObject class supports two execution modes

A 'normal' mode where only the object structure is built and an 'extended' mode where the structure is built and the content of an instance is retrieved. For example, in 'extended' mode, the structure of our sample 'employee' object is created (the master table, the child tables, the links and fields) and, if the id of an employee record is provided, the data of this employee are retrieved.

In 'normal' mode, the following functions are executed:

• eDObject(): the constructor, set the structure or skeleton of the object;
• buildKeys(): build the lnks between the master table and its children inside the structure;
• runKeyQuery(): get the possible values of the keys (mainly used by the Form Builder component);
• buildConstraints(): build the constraints that may be applied to the links defined by the keys.

In 'extended' mode, the following functions are executed:

• eDObject(): same as above;
• buildKeys(): same as above;
• buildConstraints(): same as above;
• buildObjectQuery(): build the SQL query that will be used to get the content of an object instance;
• runObjectQuery(): execute the SQL query to get the content of an object instance.

Those were the main principles. If you want more technical information eDObject is fully documented and an HTML documentation (similar to JavaDoc) is available in the eDToolbox package.

Going Further

The integration of the eDObject concept in the eDToolbox package is ongoing. Since the version 2.1, the first versions of eDFormBuilder and eDObjectSave are included in the package and will give you the opportunity to see the first results of this initiative to create a logical object layer with PHP and MySQL. New versions of this article as the developments are making progress. You can stay informed by subscribing to the eDConnection newsletter.

Print page | all | view