Php – What are the advantages to using SQL query builders

(Almost) all applications benefit from ORM.

First, I don't agree with the advantages you list for ORM.

• Using ORM doesn't necessarily mean you don't need to know SQL. A knowledge of SQL will help understand what the ORM tool is actually doing, which is especially useful during debugging. Moreover, SQL may actually be required to develop complex queries that are beyond the capability of your chosen ORM.
• And as you say, portability is rarely a concern in real life.

Instead, the real benefits of ORM are:

• ORM saves programmer time because it saves writing tons of CRUD logic in SQL
• many ORMs include complex caching logic etc. that is difficult to write and debug. As well as saving time this can enhance reliability and maintainability of your application (or at least save you the time it would take you to achieve the same results)
• the best ORMs have a community of users who actively develop, maintain and support the product. The community around custom SQL is, at best, somewhat less focused on the problems we need to solve.

As you comment, one down side of ORM is a loss of performance. However, this can usually be offset by spending more hardware.

Typically, programmer time is more expensive that hardware, so ORM is genrally a good option instead of hand-coding SQL.

ORM is best for applciations with a lot of fairly simple CRUD database logic. ORM is less effective for:

• Applications that need little / no database access.
• Applications that are largely dependent on complex queries and very little simple CRUD logic
• Situations where performance is critical, but where there is no possibility of deploying faster hardware

In my experience, these situations are rare. Hence my answer.

C. J. Date goes into detail about this in Chapter 7 and Appendix B of SQL and Relational Theory. You're right, there's nothing in relational theory that prohibits an attribute's data type from being a relation itself, as long as it's the same relation type on every row. Your example would qualify.

But Date says structures like this are "usually--but not invariably--contraindicated" (i.e. a Bad Idea) because hierarchies of relations are asymmetric. For example, a transformation from nested structure to a familiar "flat" structure cannot always be reversed to recreate the nesting.

Queries, constraints, and updates are more complex, harder to write, and harder for the RDBMS to support if you allow relation-valued attributes (RVA's).

It also muddies database design principles, because the best hierarchy of relations isn't so clear. Should we design a relation of Suppliers with a nested RVA for parts supplied by a given Supplier? Or a relation of Parts with a nested RVA for suppliers who supply a given Part? Or store both, to make it easy to run different types of queries?

This is the same dilemma that results from the hierarchical database and the document-oriented database models. Eventually, the complexity and cost of accessing nested data structures drives designers to store data redundantly for easier lookup by different queries. The relational model discourages redundancy, so RVA's can work against the goals of relational modeling.

From what I understand (I have not used them), Rel and Dataphor are RDBMS projects that support relation-valued attributes.

Re comment from @dportas:

Structured types are part of SQL-99, and Oracle supports these. But they don't store multiple tuples in the nested table per row of the base table. The common example is an "address" attribute which appears to be a single column of the base table, but has further sub-columns for street, city, postal code, etc.

Nested tables are also supported by Oracle, and these do allow multiple tuples per row of the base table. But I am not aware that this is part of standard SQL. And keep in mind the conclusion of one blog: "I'll never use a nested table in a CREATE TABLE statement. You spend all of your time UN-NESTING them to make them useful again!"