I've been thinking of creating an online database creator (think DabbleDB, Zoho Creator, Intuit Quickbase, etc.).I've been thinking of a couple solutions:Have 4 database tables: Tables, Rows, Columns, Items. To create a database as a user, you'd go to the Add New Table page, give a name and type to as many columns as you want, then save. Then you'd be taken to a page which displayed a list of rows that had items inside that corresponded with the columns. The problem with this approach is I can't figure out how to link the item with both the row and the column.Create an actual database table for each user, and when they insert a new column ( or field), it would just a database INSERT. Cons: I think this would be slow and expensive (correct?), not to mention would make a feature like how DabbleDB did relationships between tables very hard to implement.So what is the best way to build this? Or is there a better way than even the two I've mentioned.
Creating a online database creator
design;design patterns;database;sql
Doing #1 alone means that when you want to do queries against your tables of tables, you're going to have to develop the code to do it yourself that will result in some very gnarly JOIN and/or UNION clauses.  You will eventually end up implementing a database on top of your database.  This is not a road you want to travel unless you have lots of spare time and money and a burning urge to reinvent the wheel.If I understand #2 correctly, that has the same problems with each user's data split out into otherwise-identical tables.  This is something that an index on a single table can accomplish with ease.  So you probably don't want to go down this road, either.What you want is a hybrid approach where you have tables of data about your data (metadata) and tables that hold the data itself.  When the end user creates or alters a table (meta table), your software creates or alters a corresponding database table.  Everything that ends up in your metadata is assigned an identifier that's safe for the underlying database, so tables are named T1, T2, etc. and columns are named C1, C2, etc.When a query (e.g., Get me every foo from table bar where the corresponding baz is 5), look up the bits and pieces in your meta tables and use them to form a query like this:SELECT C1 FROM T1234 WHERE C2 = 5This will allow you to take advantage of all of your underlying database's performance-enhancing abilities.  Part of your metadata, for example, could be a list of what columns are going to be queried, and that could in turn be used to create indexes.