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!"
When you have too many specialized query functions you could try breaking them into composable bits. For instance
$posts = posts()->joinWithComments()->orderBy("post.post_date")->first(5);
There is also a hierarchy of abstraction levels you might find useful to keep in mind. You have
- mysql API
- your mysql functions, such as select("select * from posts where foo = bar"); or maybe more composable as
select("posts")->where("foo = bar")->first(5)
- functions that are specific to your application domain, for instance
posts()->joinWithComments()
- functions that are specific to a particular page, such as
commentsToBeReviewed($currentUser)
It pays a lot in terms of ease of maintenance to respect this order of abstractions. The pages scripts should use only level 4 functions, level 4 functions should be written in terms of level 3 functions, and so on. It's true that this takes a bit more time upfront but it will help keep your maintenance costs constant over time (as opposed to "oh my gosh they want another change!!!")
Best Answer
If information like how many comments, up-votes, favorites a post have is frequently used, you are better off storing these counts somewhere; querying the DB every time for number of comments in a post, number of followers a user have, etc. may quite soon become your performance bottleneck.
Two common approaches exist:
Add column/field for
Posts
in the database that store the comment count. when new comments are added, increase that counter field by one (synchronously), or update that count every hour/day (asynchronously).Use cache (memcache, redis) to store those numbers to reduce database queries. Memory cache are usually required for high traffic sites, anyway.
Either way you can weave it into your ORM / Abstraction Layer, so there won't be too much additional code involved. For advantages/disadvantages, there are plenty of discussions/debates around this issue on the net. Google it for thorough and detailed explanations :)