Andrew's ramblings

ColdFusion 9 ORM and mapping an older database

Tags: Coldfusion, CFORM

After having a bit of a debate on a mailing list over the last few days, I thought I would blog a bit more about this problem. But lets get this right first, it isn't a problem so to speak but it could be if you are not aware or are just learning the new ColdFusion 9 ORM.

I am going to place myself into the shoes off someone who might be learning database design, and ColdFusion 9 ORM at the same time.

But before I do lets take a quick look at someone who is in the learning phase of their development, the number one thing they are going to do is learn by example. And by example I refer to looking at others works that are out on the internet, either by tutorials or by open sourced projects.

So what was the debate, well it was over the fact that when designing an Entity in ColdFusion 9. You have to provide an ID property to your entity, and that caught one user out.

I think everyone knows what an RDBMS is, but if you don't then an RDBMS adds the additional condition that the system supports a tabular structure for the data, with enforced relationships between the tables.

However that doesn't mean people design databases the way they should either, and it was made clear by Brian Kotek and I quote what he has said. "the "norm" is a database with tables that have a primary key, since that's how databases have been intended to work since the relational model was invented. "

What he has quoted here is actually correct, and by the normal definition he is wrong. Not everyone who has designed a database actually conforms to this correct way of designing a database.

I made a proposal to Adobe, that certain things be taken away from the user having to actually type this into the entity. Not because it is not needed, but because they are and they can have a default that can be classed as given by the CF-ORM. That will make sense a little later.

Another problem with relationships in an RDBMS, is that for effective indexing of the database it should have foreign keys to tables with primary keys. However that is also not always followed in design by a lot of people either, either because they didn't think of it, didn't know about it or plain though they would do it later and ended up forgetting about it.

However not all databases support foreign keys either, for example if you are using MySQL with the MyISAM engine this will not support foreign keys. And if you are unlucky enough to inherit a database that is using this engine in MySQL then ColdFusion 9 will give you nothing but headaches when it comes to creating mapped entities to these type of databases.

To illustrate my point, I am going to use a popular open sourced ColdFusion application. This application has 13 tables created by a script provided by the author. Out of those 13 tables 6 of them have absolutely no primary keys, and this is a typical example of what I am going to use as my example when I wish to learn when using ColdFusion 9 ORM.

Lets look at just one of these tables SQL statement.

You can tell by the script above that it is not setting a primary key, and has set the database to use the engine MyISAM. Now if I was to try to put this into ColdFusion as a persistent CFC, I would type exactly what I see, and even though I personally know better. Someone who is learning will not.

So the first thing that is going to happen in this case is ColdFusion will error, the question is should it error or should it give the defaults under the hood by ColdFusion?

I believe that ColdFusion should adopt what GORM adopted here, and that is if you don't provide the ID field or property in ColdFusion's case, that it creates it for you. I have heard many people say that they don't won't ColdFusion creating extra fields in existing databases.

However the reality is that it must, whether the developer adds it or ColdFusion adds the field to the database it is required for the ORM to work. But it is also more than that, it also means that a new developer learning isn't presented with an exception thrown for no real reason. Seasoned developers will know straight away why it is thrown, but if you are learning the database design, concept and learning ORM you will not understand why an exception is thrown.

The other thing to remember here is that GORM would have gone through this, but from what I have heard by some of the ColdFusion community they are thinking only about themselves. I on the other hand have not only thought about me, but I have thought about the user who is going through the learning curve.

I really hope Adobe looks at this, and begins to think more about those beginning to learn and stop short of those who are against this because they are seasoned developers. Because at the end of the day we should be giving the developer a cleaner, more richer experience where his learning curve can be guided along the way. Something Adobe is yet to fully take on board, right from the go and not later.