Extensibility Part II - Hot properties

After my look at the past, let’s go back to day to day operations, I closed a deal a few weeks ago for a real estate web site based on Joomla. We build ‘cheap’ here using one of these little extensions that are  on the Joomla market place: Hot Property (HP).

For each property, HP provides a limited number of fields. Each one is implemented as a column in the real estate properties table. Yet the developer had the idea to make the software extensible, so you can add more fields to your properties descriptions. Surprisingly all these fields go in a separate table…  where you find fields id and values and the id off the real estate property.

Obviously, SQL querying on these is a nightmare... (I exaggerate again, at least we can say things are more complicated and imply joins that are source of performance drops and misunderstanding - e.g when customers attempt to build their own reports).

As you can see, values are stored as text (why the hell have they invented all these data types – may be a precursor of XML). I am putting here the layout of the values table. Another table contains the definition of the extra-fields. Strangely, while we have just a foreign key between the extra fields and the property, HP lets only one field (of a given type) per property.

It is difficult to know why a developer took a path or another. This is not a criticism on HP -It is a very affordable and workable Joomla component. I see anyway, a few reasons…

1. Very few people like programming the DDL (Data Definition Language). 
2. Attempting to shield from the database, HP is written with a framework including a primitive (sorry to say) persistence layer. Yet if PHP is dynamic the PHP model is statically linked with MySQL using PHP definitions.  
3. Finally reflection is something that many programmers do not envisage – among the reason is that again DDL and meta-schema is not that standard… 

All that can be combined in one  DDL+Reflexion+Persistence layer (ORM) is still a problem. Choosing at which moment you decide in your layers to be static is a tough decision…

In the case of HP, extensibility could be much higher since the application is basically a CRUD thing. The gotcha in DB application design is there : when adding processing, logic well you are obliged to statically bind your code (even if you don’t realize it, just a reference in  the code and the magic of dynamic reflection is broken) and the persistent DB model.

I like this subject that much that I am afraid… it will be continued again!