Tony Marston's Blog About software development, PHP and OOP

Object Relational Mappers are EVIL

Posted on 20th August 2007 by Tony Marston
Introduction
Relational Theory vs Object Oriented Design
- Database Normalisation
- Object Oriented Design
- Incompatible Differences
The background to my approach
How do you read from more than one table?
How do you write to more than one table?
The results of my approach
References
Comments

Introduction

The common computer application comes in two parts: program code (typically object oriented) which allows users to view and maintain different sets of data, and a database (typically relational) which allows the data to persist between executions of that program code. Both of these components should have some sort of structure (if they don't then you are in BIG trouble), but although they dealing with exactly the same data, each of them is designed and constructed using totally different principles:

As a consequence of these two design techniques being totally different, the structures produced from them are also totally different, so much so that they are totally incompatible. This incompatibility is known as the Object-Relational impedance mismatch, and is why the the Object-Relational Mapper (ORM) was invented. An ORM is a component which sits between an in-memory object (the computer program being executed) and a relational database in order to convert the structure of one into the structure of the other in all communication between the two. However, building an ORM is not an easy process. Martin Fowler says the following in his article OrmHate:

The object/relational mapping problem is hard. Essentially what you are doing is synchronizing between two quite different representations of data, one in the relational database, and the other in-memory.

In solving the problem of the incompatible structures the introduction of an ORM produces problematic side effects of its own. Because it is an additional component it involves the following:

Eventually the effort in maintaining this particular "solution" can be just as large, if not larger, as the original problem it was meant to solve. This leads to a fundamental question:

If a solution has side effects which are just as significant as the problem it was meant to solve, then is it the right solution?

Is there a different solution which produces fewer side effects? How about an obvious one - if the difference between the two structures causes a problem, then why can't the two structures be made less different? This will remove the need for an ORM as well as any problems caused by using an ORM. This is not a new idea. Some will say that it has already been tried, and failed, therefore it should not be considered as a possible solution at all. Some say that the differences between relational theory and OOD are so great that it is impossible to combine them without sacrificing some of the fundamental features that OO provides, therefore there is no choice but to employ an ORM. Let us examine this in more detail.

Relational Theory vs Object Oriented Design

As I mentioned earlier a computer application has two parts, software and a database, which both work with and manipulate exactly the same data. The only difference is that one does it in memory while the other does it to disk. However, the design of the software (Object Oriented Design) and the design of the database (Database Normalisation) follow different rules, and the result of applying these different rules is a different data structure. Why on earth should these two sets of rules produce different and incompatible structures when applied to exactly the same data? Surely this would indicate that one of these sets of rules is broken and in need of serious repair? Let us examine these rules in more detail.

Database Normalisation

A well-structured relational database is designed by applying a process called Database Normalisation, which starts with First Normal Form (1NF) and progresses all the way up to Sixth Normal Form (6NF). The principles of normalisation are simple, common sense ideas that are easy to apply. Each can be defined in a single sentence, with practical "before" and "after" examples showing how they can be applied. A design cannot be considered for the Nth Normal Form until it has first passed through the N-1 Normal Form. Although some of the higher levels of normalisation are optional (this is known as de-normalisation), even if a designer stops at 3NF he must have progressed through 1NF and 2NF to get to that point.

Object Oriented Design

Object Oriented Design (OOD), combined with its physical implementation in Object Oriented Programming (OOP), does not have a clear and concise set of rules or processes. There is no "step 1" to "step 6". All it has is a basic set of "features" which must exist in a language in order to support a method of programming using objects. These features can be defined as follows:

Object Oriented Programming Writing programs which are oriented around objects. Such programs can take advantage of Encapsulation, Inheritance and Polymorphism to increase code reuse and decrease code maintenance.

Note that the effectiveness of your implementation can be measured by the amount of reusable code that you produce. The more reusable code you have at your disposal then the less code you need to write to get the job done, the less time it will take and the more productive you will be.

Class A class is a blueprint, or prototype, that defines the variables and the methods common to all objects of a certain kind.
Object An instance of a class. A class must be instantiated into an object before it can be used in the software. More than one instance of the same class can be in existence at any one time.
Encapsulation The act of placing data and the operations that perform on that data in the same class. The class then becomes the 'capsule' or container for the data and operations. This binds together the data and the functions that manipulate the data.

More details can be found in OOP for heretics

Inheritance The reuse of base classes (superclasses) to form derived classes (subclasses). Methods and properties defined in the superclass are automatically shared by any subclass. A subclass may override any of the methods in the superclass, or may introduce new methods of its own.

More details can be found in OOP for heretics and Using "IS-A" to identify class hierarchies.

Inheritance is used for "is-a" relationships.

Object Composition A way to combine simple objects or data types into more complex ones.

More details can be found in Using "HAS-A" to identify composite objects and Use inheritance instead of object composition

Object Composition is used for "has-a" relationships.

Polymorphism Same interface, different implementation. The ability to substitute one class for another. This means that different classes may contain the same method signature, but the result which is returned by calling that method on a different object will be different as the code behind that method (the implementation) is different in each object.

More details can be found in OOP for heretics

There are other "features" such as multiple inheritance, visibility (private/protected/public), interfaces, exceptions, et cetera, but these are all later add-ons and therefore not fundamental to OO.

The major problem with these features is that there is no simple progression from "not object oriented" to "object oriented" as there is from "not normalised" to "normalised". Some of these features are in fact optional and not mandatory - it is possible to write a class which does not have inheritance, or which does not share any methods with other classes (polymorphism). It is even possible to split an entity's data and/or operations across more than one class, thus breaking encapsulation. The bare minimum then is to have a class from which you can instantiate one or more objects.

Because there is no simple and verifiable step-by-step progression from "not object oriented" to "object oriented", just a set of features (some of which are optional), it is left up to the individual to decide how to implement these features. Unfortunately this leads to the situation where 100 different programmers, when given the same problem, will produce 100 different implementations. There is no single, universally-accepted opinion on what OOP is and what it is not, or what constitutes "good OOP" or "bad OOP". Because there is so much interpretation involved, this inevitably leads to a great deal of mis-interpretation. As an example, before you can design a class you are supposed to go through a process called "abstraction", but what does abstraction actually mean? Unfortunately the dictionary provides two different definitions:

  1. A statement summarizing the important points of a text. To reduce to the essential details. Summary, synopsis, précis, résumé, outline, abridgement, condensation, digest.
  2. Thought of or stated without reference to a specific instance. An ideal or theoretical way of regarding things. Separated from matter, practice, or particular examples; not concrete; insufficiently factual; unreal; hypothetical; abstruse; difficult to understand; incomprehensible.

The result of one is a summary of essential points, the result of the other is unreal and difficult to understand. If the result of this abstraction process is wrong, it surely follows that every step taken from that point is a step in the wrong direction, even more so when every step taken does not have to follow an easily-verifiable formula.

The failure of some (most?) OO programmers to understand what the term "abstraction" really means causes them to reach conclusions and make decisions which are, IMHO, fundamentally wrong, such as:

Abstract concepts are classes, their instances are objects. Classes are supposed to represent abstract concepts. The concept of a table is abstract. A given SQL table is not, it's an object in the world. Having a separate *class* for each table is therefore bad OO.

I have to disagree. This is a prime example of someone totally misunderstanding the terms "abstract", "concept" and "real". When I see the terms "the concept of an SQL table" and "a given SQL table" I read them as follows:

An SQL table is not an object, it is merely the blueprint for a type of object, and is therefore a class. It is not until you create a record in that table that you have an actual instance of that blueprint. Thus a table definition is a class while a table row is an instance of that class. The terms "concept" and "real" can be implemented as follows:

While the abstract class may be quite large as it needs to contain code for every possible SQL query, each concrete subclass is very small as it only identifies the barest of details for a single specific database table. When instantiated into a object this combines all the possibilities of the superclass with the actualities of the subclass.

It would appear, then, that having a separate class for each table is not so bad after all. In fact, if you examine my critic's statement you will see that it is his interpretation of that statement which is questionable:

The concept of a table is abstract.

This is why I have an abstract table class which identifies every operation which can be performed on any (as yet unspecified) database table.

A given SQL table is not, it's an object in the world.

This is why I have a concrete table class for each database table, which inherits from the abstract table class. Objects are instantiated from a concrete class, not an abstract class. It really is that simple, and definitely not as complicated as some OO proponents would lead you to believe.

Incompatible Differences

While there are undeniable differences between relational and OO theory, too many of today's OO programmers spend far too much time in exaggerating those differences and complaining that they are totally incompatible. I am sure that they only do this in a feeble attempt to justify their perceived need for an ORM to act as an intermediary between the two. If you actually examine these so-called differences in greater detail you will see that it is actually possible to diminish their scale - in other words, to make molehills out of mountains. Take a look at the list of "differences":

  1. Databases do not have classes. While they do not use the word "class", if you look at this definition you will see that a class is nothing more than a blueprint that defines objects of a certain kind. If you examine the DDL (Data Definition Language) which describes a database table, is it not fair to say that the DDL is a blueprint for all records (objects) which may exist in that table?

    "But what about the methods?" I hear you say. Each table definition does not need to define the methods that can be performed on that table for the simple reason that the same basic methods - create, read, update and delete - are universal across all tables. You may point out that a class can have many more methods than these, but I would point out that ANY method, regardless of its complexity, is nothing more than a variation of one of these four.

  2. Databases do not have instances. If the DDL of a database table can be considered to be the same as a "class", then surely a physical record within that table can be considered to be the same as an instance of that DDL? That's not a difficult concept, is it?
  3. Databases do not have encapsulation. If you look at this definition and disregard the fact that no methods are defined for each table as the four methods - create, read, update and delete - are universal across all tables, then a database table is no different from a class. You can put all the data for the same entity into a single table - customer data goes into the CUSTOMER table whle product data goes into the PRODUCT table - just as you can put in into a single class, or you can split that data across multiple tables just as you can split it across multiple classes. What you can do in one you can do in the other, so what's the big difference?
  4. Databases do not have inheritance. If you look at this definition you will see that inheritance is merely a technique for sharing the methods and properties of a superclass with a subclass. So how do you share data in a database? Instead of the word "extends" use "foreign key" instead and it all becomes clear. All the shared data is placed in a table of its own, known as a "foreign table", and is referenced through a pointer known as a "foreign key" (i.e. the key to an entry on the foreign table). This makes it easy to connect the two tables together at runtime by using a relational join.

    In a lot of tutorials on OO I see examples of class hierarchies created just because something IS-A type of something else. For example, "dog" is a class, but because "alsatian", "beagle" and "collie" are regarded as types of "dog" they are automatically represented as subclasses. This results in a structure similar to that shown in Figure 1:

    Figure 1 - hierarchy of "dog" classes

    oop-for-heretics-05 (1K)

    With this approach you cannot introduce a new type (breed or variety) of dog without creating a new subclass which uses the "extends" keyword to inherit from the superclass.

    This is not how it is handled in a database. The DOG entity would have its own table, and in my software each table, because it has its own business rules, would have its own class. Each table can handle multiple rows, so its class should do so as well. In a database the idea of being able to split the contents of the DOG table into different types, breeds or varieties would not involve separate tables, it would simply require an extra column called DOG-TYPE which would be just one of the attributes or properties that would be recorded for each dog. If there is no need for a separate table for each DOG-TYPE I can see no reason to have a separate subclass for each DOG-TYPE.

    If there were additional attributes to go with each DOG-TYPE then I would create a separate DOG-TYPE table to record these attributes, and make the DOG-TYPE column of the DOG table a foreign key which points to the DOG-TYPE column of the DOG-TYPE table, which would be its primary key. This would produce the structure shown in Figure 2:

    Figure 2 - structure of "dog" tables

    oop-for-heretics-06 (1K)

    With this design all the attributes of a particular type/breed of dog are stored on the DOG-TYPE table, so instead of a separate subclass for each DOG_TYPE I would have a separate row on the DOG-TYPE table. When reading from the DOG table you can include a JOIN in the SQL query so that the result combines the data from both tables. This is how you can "inherit" attributes in a database. The introduction of a new type of dog requires no more effort than adding a record to the DOG-TYPE table. There are no changes required to the software, no new classes, no new screens, no new database tables, no nothing. From a programmer's point of view this simple 2-table structure is far easier to deal with than an unknown number of subclasses.

    There may be cases where the number of different "types" is fixed, but the difference between them are quite significant and therefore require different table structures, in which case I would use a structure similar to what is shown in Figure 3:

    Figure 3 - hierarchy of tables (1)

    object-relational-mappers-are-evil-003 (1K)

    Here a PARTY can either be an ORGANISATION or a PERSON. The PARTY table holds the data which is common to both, while the other tables hold the data which is specific to that type. Now, if both ORGANISATION and PERSON can be broken down into different types I would use the structure shown in Figure 4:

    Figure 4 - hierarchy of tables (2)

    object-relational-mappers-are-evil-004 (1K)
  5. Databases do not have polymorphism. If you look at this definition you will see that polymorphism is nothing more than the ability for several classes to share the same methods. When you consider the fact that methods are not defined for each table in a database for the simple reason that the same set of methods - INSERT (create), SELECT (read), UPDATE and DELETE - is common across all tables, it is plain to see that all tables automatically share exactly the same methods. Does this fit the definition of polymorphism or what?
  6. Databases are not object oriented. So what? Something does not have to be OO in itself for it to be accessible from an OO program. The HTML and XML languages are not object oriented, but does that prevent an OO program from generating HTML or XML documents? The SQL language is not object oriented, but does that prevent an OO program from generating SQL queries? I think not. The real complaint is that it is not possible to take an in-memory object and simply dump it to a relational database in its current structure without any additional effort. There is all that messy SQL to deal with, all those SQL queries to generate. SQL is not object oriented, so many OO programmers haven't a clue how it works. SQL requires knowledge of the database structure, and efficient database structures are designed using a theory which again is not objected oriented, so many OO programmers haven't a clue how it works. What these poor demented souls dream of is the introduction of an OODBMS which follows their thinking more closely. Unfortunately for them the OODBMS has a long, long way to go before it replaces the solid, well proven RDBMS. This topic is discussed in more detial in OO Design is incompatible with Database Design.

Many OO programmers just haven't a clue about relational databases and the universal SQL language, so they do not see the benefit of making their object structure as close as possible to the database structure. Instead they have this nasty habit of designing structures which are so obtuse, so off the wall, so far removed from the more sensible, normalised structure of the database, that it is virtually impossible to make the two communicate with each other without the intervention of a translation mechanism or ORM. To them their class structure comes first, and the database structure is left till last as a mere "implementation detail", an afterthought. In my humble opinion a good database design is the foundation for a good application, and anyone with more than two brain cells to rub together will tell you that you always start any construction with a solid foundation. Anything else is a disaster waiting to happen.

It appears that I am not alone in this opinion. The following book title was found on www.oreillymaker.com:

Figure 5 - Book for ORM fanatics

hybernate (66K)

Instead of exaggerating the differences between the two design methodologies and making the use of an ORM virtually mandatory, my personal approach is to minimise the differences, or preferably eliminate them altogether, and try to get them to work with each other as closely as possible, thus making the use of an ORM totally redundant. I achieve this with one very simple technique - every table in the database has its own class. One table, one class, no exceptions. My critics (of whom there are many) are quick to come up with arguments such as:

Speaking as a programmer who has built several systems which involve OO software communicating with a relational database I can state quite categorically that it can be done for the simple reason that I have done it. Perhaps my approach is more successful because I had over 20 years of experience in software development before moving into object oriented programming, and this experience made it easier for me to get to grips with how best to implement the OO paradigm. Contrast this with a lot of todays newbie programmers who have zero experience and are taught utter rubbish by clueless dunderheads who have the nerve to call themselves "experts". Instead of being allowed to experiment with various approaches to find out which one works best they are told "there is only one way", and so they follow like sheep and are never allowed to learn anything better. Those people only know what they have been taught, whereas I know what I have learned through experience. Believe me, there is a BIG difference between the two.

The background to my approach

Unlike so many others I was not a clueless newbie when I jumped into the quagmire of object oriented programming. I had decades of experience behind me, and I used this experience to separate the wheat from the chaff. I started off my programming career using COBOL, that well known procedural language, using indexed files, hierarchical and network databases, and then some 16 years later I moved to UNIFACE, a model-driven and event-driven language using relational databases. During all this time I learnt the following valuable lessons:

  1. Start with a properly designed (i.e. normalised) database. I have worked with a few badly structured databases, so I know from personal experience what a disaster they are.
  2. Follow up with a software design that works with, not against, the structure of the database. Not only should the software have structure (I have seen my share of unstructured COBOL code, thank you very much) but that structure should actually mirror the structure of the database (as taught at a Jackson Structured Programming course I attended in 1979). I do not waste my time re-examining the data using "is-a" and "has-a" relationships in order to build a complex class hierarchy as every database table, without exception, automatically has its own class.

The majority of this experience was with software houses where the job involved designing and building an application for one customer before moving on to another application for a different customer. This was a high pressure environment which involved bidding against other software houses for the contract, and then having to complete that project to budget and within timescale. No room there, then, for wooly-headed theories which did not cut the mustard.

In 2002 I decided to teach myself PHP so that I could move into web development, and because it had OO capabilities I decided to learn about OOP as well. From reading various books and online tutorials I discovered that the basic principles of OOP are encapsulation, inheritance and polymorphism, so I tried to combine my decades of previous experience with these new principles in order to write software. My starting point was to rewrite a development framework which I had originally designed and written in COBOL (using a single tier architecture) in 1985, then rewrote in UNIFACE in the 1990s, firstly using a 2-tier architecture then again using a 3-tier architecture when that capability was introduced into the language. My original COBOL framework was successful in reducing developer effort as it removed a lot of boring, repetitive coding and provided a lot of features "out of the box". My first 2-tier rewrite in UNIFACE was better, and my 3-tier rewrite better still, so I wanted to see if PHP+OOP could continue this trend. I'm happy to say that I was not disappointed. I went through the following sequence of events:

  1. The UNIFACE language had a feature called component templates which made it easy to generate new components from a pre-defined template or pattern. It worked on the premise that each component can be categorised by structure, behaviour and content, with the template built around a particular structure (screen layout) and behaviour (code). A functioning component could be created by combining a template with a particular entity (database table). As I had found this feature a great aid to rapid application development I wanted something similar in my PHP framework, but as it did not exist I had to invent it. I devised a catalog of transaction patterns where the structure is provided by an XSL stylesheet, and the behaviour is provided by a page controller. By combining a pattern with a database table class (the content) the result is a functioning transaction.
  2. I created a database table, then wrote a family of forms to view and maintain the contents of that table. I wrote a separate controller script for each of the LIST, SEARCH, INSERT, UPDATE, DELETE and ENQUIRE components, but made them all communicate with the same table class.
  3. I constructed a class file for this table, and filled it with the code to satisfy the requirements of all the members of that forms family. I then copied this file and made it do exactly the same thing for another database table. It was then a straightforward exercise to compare these two files to see where the same code was duplicated, and to move this common code to a superclass from where it could be shared via the mechanism of inheritance. Because I was able to move a vast amount of common code into the superclass I found that each table subclass turned out to be quite small - it contained barely more than the table name and its database structure.
  4. Originally the superclass contained all the logic to access the database, but I eventually split this off into a separate Data Access Object. The original DAO was for dealing with MySQL, but I have since written additional classes for PostgreSQL, Oracle and SQL Server. It is possible to switch from one DBMS to another simply by changing a single configuration parameter. Unlike other implementations I have a single DAO for the entire application and not a separate DAO for each individual table.
  5. Because there are only four basic operations which can be performed on a database table - SELECT, INSERT, UPDATE and DELETE - I created a corresponding method for each operation:

    Later on I added other methods such as insertMultiple(), updateMultiple() and deleteMultiple() which could deal with any number of records at a time instead of being limited to just one.

  6. Notice that because I know that I will be accessing a relational database, and nothing but a relational database, using SQL statements that I am embracing that fact and deliberately modelling my class methods around those SQL operations. I am not going out of my way to hide the fact that there is an SQL database in the background, which makes it a lot easier when I want to communicate with that database.
  7. I decided to have a single getData($where) method for retrieving records from the database as this is infinitely flexible and can deal with any set of circumstances. Other implementations seem to favour a separate method for each combination of selection criteria, but this sounds like a lot of effort with absolutely no benefit.
  8. Even though each class deals with a specific database table, and each table has a list of columns, I decided NOT to have a separate class variable for each column. Why? The input from an HTTP request is an associative array, and it made more sense to me to pass the whole array to the business object as a single unit instead of unpicking it and using a setter method to pass in one column at a time. It is just as easy to access a column within an array as it is a class variable, so there are no disadvantages to this approach.
  9. Having a single class variable to hold all table data in an array instead of a separate variable for each column means that the array can contain any number of columns, and it is easy to add in extra columns at runtime, either by obtaining them from other tables with an SQL JOIN or by generating them with a calculation. This array may also be multi-level, in which case it can contain any number of rows. Other people seem to think that an object can only deal with one database record at a time, which then requires extra code to deal with multiple records, but my approach is simpler and more flexible.
  10. All communication between the presentation layer (page controllers) and the business layer (database objects) is performed by passing a single $fieldarray array and not by using getters and setters on individual columns. This means that none of my page controllers requires any knowledge of the database columns that may exist within any database objects.
  11. When a page controller retrieves data from a database object for transfer to the View component the single array for all the object's data is extracted and passed to a standard routine which converts it into an XML document without having any column names hard-coded anywhere. This means that I do not have to waste time with using getter methods for each individual column.
  12. The contents of the XML document is transformed into an HTML document by means of an XSL stylesheet. Originally each database table required its own set of XSL stylesheets as each of them was customised to identify which field was positioned where, and with what HTML control. Eventually the need for XSL customisation was removed as these details can be extracted from the XML document after having been specified in a screen structure file. This means that a small set of pre-written XSL stylesheets can be used to produce a large number of different screens.
  13. One big advantage of using XML and XSL to create HTML output is that the order in which data is added to the XML document has absolutely no effect on the order in which that same data is extracted and turned into HTML. This means that I can change the order in which the data is processed in an XSL stylesheet without having to change the order in which that data is written to the XML document in my program code.
  14. When each page controller activates a method on a database object this sets in motion a sequence of invariant and variable methods which go all the way to the database and back again. This sequence contains a selection of variable methods which are defined in the superclass, but which are empty. These empty methods can be copied to a subclass where they can be filled with custom code, and this custom code will be executed at runtime. This mechanism, which is an impmementation of the Template Method Pattern, allows the developer to interrupt the processing flow with custom code at any stage, as shown in this series of UML diagrams.
  15. None of my page controllers requires any knowledge of which methods are used to access which database objects for the simple reason that every database object uses exactly the same generic methods. This fits the description of polymorphism. This is totally unlike the approach I have seen advocated where each method name includes the entity name. For example, with entities called CUSTOMER and PRODUCT the method names would be getCustomer(), getProduct(), insertCustomer(), insertProduct(), etc. This method would require each controller to be tied to a specific object as the method names it used would only work on that particular object. My approach means that a controller can be used on any object as the method names are identical.
  16. None of my page controllers contains any hard-coded class names as these are passed down in a variable from a component script. The controller simply uses the variable to load the class file and instantiate an object, then call methods on that object. Because my controllers do not contain any hard-coded class names or method names they are infinitely reusable.
  17. Each database table class requires some knowledge of the structure of its associated database table in order to function properly. The structure details were built up in stages:
  18. Each database table class thus knows what data is held within that table's structure, and, with the combination of standard methods which are inherited from the abstract table class plus the use of custom methods to handle any business rules associated with that table, also contains all the operations which can be performed on that data. This fits the definition of encapsulation.
  19. Originally each table's class file and associated structure details were built manually, but eventually I was able to automate this by building a Data Dictionary. This can import its data directly from the database, then, with the press of a button, export the details to the application in the form of two files - a class file and a separate structure file.
  20. Originally all the component scripts and screen structure scripts for each transaction had to be built manually, but eventually I was able to automate this by adding a transaction generation function into the Data Dictionary. This works by choosing a database table, then a transaction pattern, and it will generate the relevant scripts at the press of a button. It also adds transaction details to the menu database so that, in most cases, the new transactions can be run immediately.

To summarise, in order to define the classes which an application needs I must first identify all the different entities and their properties, and the operations which can be performed on them. If I am developing a database application then the entities and their properties have already been defined in the database structure, where each entity has its own table and its own set of properties. Because they are database tables the only operations that can be performed on them are SELECT, INSERT, UPDATE and DELETE. It makes sense to me to use the database structure as my software structure instead of going through a separate process which produces a different set of entities, properties and methods. This has two distinct advantages:

It seems that this concept is too simple for some people.

How do you read from more than one table?

The standard answer from OO purists would be to create a new class which contains elements from all the relevant tables, but this type of solution simply is not in my repertoire. My solution incorporates any one of the following options:

  1. When dealing with a one-to-many or parent-child relationship it is quite common to have an output screen which has a separate zone for each of the two tables, such as one at the top which shows a single occurrence from the parent table, and another below it which shows multiple occurrences from the child table, as shown if Figure 6:

    Figure 6 - parent and child data in different zones

    object-relational-mappers-are-evil-005 (1K)

    There is no rule which says that a controller may only communicate with a single model (database object), so I have built my controllers to access a separate database object for each zone. In this example it will call the getData() method on the PARENT object using whatever selection criteria has been passed down to it. Only one record will be displayed, but if more than one is retrieved the screen will contain hyperlinks to scroll back and forth between them. The primary key will be extracted from the current PARENT record and used in the getData() method on the CHILD table. The number of CHILD records actually displayed on each page will be determined by the page size, which can be varied by the user. If more records are available than can fit on a single page then hyperlinks will be available to scroll back and forth between them.

    This controller can be used for any two tables which exist in a parent-child relationship as the logic is exactly the same, only the table names are different.

  2. Another way of dealing with a parent-child relationship is to include data from the PARENT table inside the zone used by the CHILD table, such as when using a foreign key on the CHILD to obtain one or more fields from the PARENT, as shown in Figure 7:

    Figure 7 - parent and child data in the same zone

    object-relational-mappers-are-evil-006 (1K)

    The most efficient way of combining data from more than one table in the same result set is to use an SQL JOIN. It is a feature of my framework that it is possible to have the JOIN statements constructed automatically based on relationship information which is obtained from the Data Dictionary. There is no rule that says that the data which is extracted from a database object must be obtained from a column within that table, so it is possible to construct a data array that contains columns from any number of different sources. It is therefore possible to construct SQL queries which are as complicated as you like as the result set which is produced is extracted wholesale, converted into XML, then transformed using an XSL stylesheet. The XSL stylesheet does not care where the data came from, as the fact that it exists within the XML document is good enough.

  3. If it is not possible to use an SQL query with JOINs to obtain the data from multiple sources in a single operation then, as a last resort, it can be performed one step at a time in the application. There are custom methods available all along the processing path which means that the contents of an object's data array can be inspected and modified very easily before the end result is exported to an XML document. This means that it is possible to instantiate an object from another database table, use this object to obtain more data, and merge that data with the current object's data. This is demonstrated in the following code snippet:
        function _cm_getForeignData ($fieldarray)
        // Retrieve data from foreign (parent) tables.
        {
            if (!empty($fieldarray['foreign_key'])) {
                $dbobject = RDCsingleton::getInstance('foreign_table');
                $data = $dbobject->getData("primary_key='{$fieldarray['foreign_key']}'");
                if (count($data) > 0) {
                    $fieldarray = array_merge($fieldarray, $data[0]);
                } // if
            } // if
    
            return $fieldarray;
    
        } // _cm_getForeignData
    

How do you write to more than one table?

The following options are available:

  1. If there are two tables involved in a parent-child relationship as shown in Figure 6 then the controller will already be using separate objects for the PARENT and CHILD tables. The POST array will contain data for both tables, but these are easily distinguishable as the data for PARENT will not be indexed by row number whereas the data for each CHILD occurrence will be indexed by its row number. It is therefore quite straightforward for the controller to pass the unindexed array to the PARENT object and the indexed array to the CHILD object.
  2. If the array which is passed to an object contains data which must be written to a different table then the process is no more complicated than reading from a foreign table. Let us take the example where the whole POST array is passed to an object for "table1", but part of this data must be written to "table2":

It may not be rocket science, but it works, and thus adheres to the KISS principle.

The results of my approach

Martin Fowler, in his article OrmHate wrote the following:

In-memory data structures offer much more flexibility than relational models, so to program effectively most people want to use the more varied in-memory structures and thus are faced with mapping that back to relations for the database.

I totally disagree. I have been building database applications for decades. I have built frameworks for building database applications in three different languages, and each of these frameworks has specifically targeted the database structure and not some airy-fairy, arty-farty "real world" conceptual representation which is as divorced from reality as it is possible to get. My latest web application framework has the following characteristics:

As a result of this approach I now have a Rapid Application Development Toolkit for building Administrative Web Applications. Building a new web application is now an easy process:

  1. Start with a properly normalised database.
  2. Import the database into the Data Dictionary.
  3. Customise those details as required. This includes defining all relationships with other database tables, and adding information which can be used by the presentation layer.
  4. Export from the Data Dictionary to the application. This will create the class files and the structure files for each database table.
  5. Decide which transaction patterns are required, then go into the Data Dictionary, select a table, select a pattern, then press a button to have the code generated for you. Details are also added to the menu database, so it is also possible to run the generated script straight away.

Using this framework it is therefore possible to generate a web application to maintain the contents of a number of database tables without having to write any HTML or SQL. Indeed, the initial maintenance screens do not require the writing of any code at all. The only time that it is necessary to write any code is to customise the screen layout, or to modify a database table class to include any business rules or to override the default behaviour.

All this and not an ORM anywhere in sight, so don't tell me that it cannot be done.


References

The following articles describe aspects of my framework:

The following articles express my heretical views on the topic of OOP:

These are reasons why I consider some ideas to be complete rubbish:

Here are my views on changes to the PHP language and Backwards Compatibility:

The following are responses to criticisms of my methods:

Here are some miscellaneous articles:


counter