I have an environment that supports both dictionaries (json style) and databases (not relational or anything, just formatted data by row and column). My application doesn't really need database functionality, butI'm somewhat more comfortable with the database system than I am with dictionaries. Is there a major performance advantage to dictionaries? What situations are there where a dictionary is better than a database?
Dictionaries vs. Databases – Relative Advantages
databasedictionary
Related Solutions
The most important thing is to be absolutely certain where the bottleneck is now for a given number of representative requests as you cannot switch databases.
If you do full table scans, you need appropriate indexes.
If you wait on I/O you need more memory for caching (Jeff Atwood recently mentioned that 24 Gb systems were reachable on desktop systems).
If you wait on CPU you need to see if your calculations can be optimized.
This requires a pointy DBA-hat and a Operating System-hat, but is worth it to ensure you are barking up the right tree.
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!"
Best Answer
If you want to know whether there is a performance advantage, the best thing to do is measure it yourself. The performance depends a lot on the type of data, the language, the amount of data, etc.
It's impossible to give a blanket statement as to when dictionaries are better than databases. Again, it depends on the data, the language, etc. Roughly speaking, dictionaries are better for simple and small datasets, and databases are good for complex and large data sets.