In an application that allows users to define their own tables and columns (details of that are not the focus of this question, but imagine a tool that lets users create their own forms and automatically handles storage, just to get the idea) is it better to store each table and column they create in a "table" table and a "column" table, or to dynamically create tables and columns?
This "one table to rule them all" question is somewhat related, but in that case the "table" table is more clearly a bad idea. In this case, if you don't go that route then all your queries will have to be dynamically generated.
Even though the argument "don't write yet another application designer tool for non-programmers, so you can keep adding features until you are basically having users develop in a crummy version of, say, Visual Studio, using your custom crummy language" is pretty reasonable, I've had a lot of projects with some version of the user defined tables requirement and this question is about how to implement it. Whether to implement it is another question, but probably too specific to particular applications to be a good question here.
Best Answer
A generic database design limits the proliferation of new tables as the number of forms and data elements grows. On the other hand even the simplest query involved 3 tables, in our case.
Queries indeed got very mind boggling. After 3 years of this I became of the opinion that explicit tables was better. I'd rather deal with more tables that better reflect the business form/model - that I can understand just by looking at them; vice 1/2 the tables but with multitudes of meaningless join tables requiring astonishing long, nested queries to make even the most rudimentary sense out of the data. You actually start memorizing the meaning/context of individual identity column primary keys!
Finally, we had evolved to where we had that one table to rule them all so as to get away from some of the inherent complexities (and bad coding and design decisions of the past) in the existing design and evolving user requirements. Nonetheless, we were certain we'd have problems as the table size went into the million+ row size.