On Object-Relationanl Mapping (ORM)

"ORM's are for basic CRUD crap.SQL is for queries that matter. My ORM usage is almost entirely limited to: get this record, change these values, save it. Anything involving complex joins/ unions gets thrown to raw SQL. Better yet, stored functions on the DB side. Postgres is awesome for this."-- fein, emphasis added
This is even so true if you're inheriting old legacy system with one of the most common database table design issues, table with many columns. Not like 10-plus columns but more like 50-plus to 100-plus columns per table. What worse, new columns were still added to these tables.

Why so many columns per table? Three reasons, lack of database normalization knowledge, weak in writing SQL join or support was poor for the chosen database system, and you're optimizing for convenient.
The last reason is the root cause of this design error.

Let me elaborate this. If you're pressing for time, you will start with a simple table design for any CRUD operations. Writing SQL queries were simple and straight forward without any speed penalty. Or you may start with proper database normalized design but with extra SQL queries and speed penalty due to weak SQL JOIN support (I am looking at you, MySQL). Hence, which one would you prefer? Off course the former choice.

To be fair, under certain circumstances, big table with many columns is the preferred design choice. This is even truer if referential integrity was not needed and you want to denormalize the table design to optimize for reading speed. In other word, you don't mind redundant data and trading speed for storage size.

For those tables with lots of columns, ORM really helps a lot during refactoring. We can slowly remove all those raw CRUD SQL without making any changes to database schema. Hence, application code was shorter, readable, and more maintainable. Is always harder and more dangerous to refactor database schema where you haven't really grok the business rules involved.

Based on experiences, setting relationship between tables using an ORM was painful and troublesome. Most ORM libraries follows a certain rigid rules of connection the relationship. Suitable for new database schema but not legacy database schema which first, does not follows standard convention, and second, contains more complex many-to-many relationships.

In summary, use ORM for refactoring legacy system, but only for simple CRUD operations. But use raw SQL for more complex SQL query like JOINs or multiple rows update or deletion.

No comments:

Post a Comment