Timeline for answer to Why don't relational databases support returning information in a nested format? by Bill Karwin
Current License: CC BY-SA 3.0
Post Revisions
16 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Nov 29, 2013 at 19:08 | comment | added | Bill Karwin | @EamonNerbonne, yes, Date also says that it's more tolerable to support RVA's in result sets, and less tolerable in the relvars (i.e. persistent tables defined in your schema). FoundationDB is another vendor that provides an extension to SQL that expresses RVA's. They use it to combine multiple rows from a joined table into one RVA in a row of the result set. See foundationdb.com/layers/sql/SQL/dml/… | |
| Nov 29, 2013 at 11:38 | comment | added | Eamon Nerbonne | This is possible for instance in PG 9.2, albeit with an unhandy syntax. See, for instance: stackoverflow.com/questions/13227142/… | |
| Nov 29, 2013 at 11:22 | comment | added | Eamon Nerbonne | For example, ORM's often cause more work for the DB-engine and unnecessarily large query results by unfolding data they see as a hierarchy into an SQL set query just to recreate the hierarchy client-side. Any query projection that's not in normal form necessarily duplicates data; this is a waste. We do so anyhow, not because normal forms aren't useful, but because that specific db-client case cares not about the model as a cohesive whole, but cares about a specific view or summary. | |
| Nov 29, 2013 at 11:15 | comment | added | Eamon Nerbonne | Let me put it this way: you can use SQL to concatenate a bunch of strings or sum a bunch of numbers. These operations are assymmetric too, but they're nevertheless useful in queryies. In theory, you could probably use SQL to build a nested, hierarchical JSON-document too; but doing so is really impractical and slow. The point isn't that such a presentation is useful to the database, it's useful to a database "consumer". | |
| Nov 29, 2013 at 11:13 | comment | added | Eamon Nerbonne | @BillKarwin that argument makes sense against using hierarchical structures in the database schema - where you can define constraints, etc. But as a query projection, you don't have any of these downsides. The whole point of a query is to extract data from your model; assymmetry is inherent in that operation. Constraints, updates etc? You're not changing anything, so none of these matter. | |
| Nov 28, 2013 at 16:43 | comment | added | Bill Karwin | @EamonNerbonne, symmetry of relational operations. For example, projection. If I SELECT some sub-attributes out of an RVA, how can I apply a reverse operation against the result set to reproduce the original hierarchy? I found page page 293 of Date's book is on Google Books, so you can see what he wrote: books.google.com/… | |
| Nov 28, 2013 at 14:08 | comment | added | Eamon Nerbonne | @BillKarwin I'm not sure which symmetry you mean here - it's just a question of projecting data into a nested format, nothing intrinsic about storage. | |
| May 19, 2013 at 8:18 | comment | added | Bill Karwin | @EamonNerbonne, this is the only way relational operations can be symmetric. | |
| May 19, 2013 at 7:59 | comment | added | Eamon Nerbonne | Isn't it slighty absurd to argue that to avoid data duplication, you must write your query in a flat, data-duplicating manner? | |
| Jul 8, 2011 at 16:17 | history | edited | Bill Karwin | CC BY-SA 3.0 |
added 912 characters in body
|
| Jul 8, 2011 at 10:45 | comment | added | nvogel | Standard SQL does support nested tables. They are called "structured types". Oracle is one DBMS that has this feature. | |
| Jul 7, 2011 at 22:52 | vote | accept | PreciousBodilyFluids | ||
| Jul 7, 2011 at 19:17 | comment | added | Bill Karwin |
Result sets and tables are of a kind. Date calls them relations and relvars respectively (by analogy, 42 is an integer, whereas a variable x can have the value of integer 42). The same operations apply to relations and relvars, so their structure needs to be compatible.
|
|
| Jul 7, 2011 at 16:15 | comment | added | PreciousBodilyFluids | I wouldn't want to actually store one relation inside another - they'd be in separate tables and denormalized as usual. I'm just asking why this sort of embedding of results isn't allowed in queries, when it seems more intuitive to me than the join model. | |
| Jul 7, 2011 at 2:59 | history | migrated | from stackoverflow.com (revisions) | ||
| Jul 6, 2011 at 22:45 | history | answered | Bill Karwin | CC BY-SA 3.0 |