3

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:

  1. 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.

  2. 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.

Improve this question
2
  • What none of them have been able to accomplish is the parent-child relationship. Very limited IMHO and regardless of how you implement it, if you could add this feature, you'll offer something they never have.
    – JeffO
    Feb 3, 2014 at 1:59
  • The first option sounds terribly cumbersome. Too far off the underlying database. You couldn't have proper indexing or relations. Can't see how this could be any faster than option two (or make it easier to implement relations). As long as you handle security issues properly option two should give you the most freedom to generate the whole set of common SQL queries from user input. Option one seems to try to emulate a database within a real database. (A lot would depend on the size of those tables and complexity of queries of course)
    – thorsten müller
    Feb 3, 2014 at 9:09

1 Answer 1

Reset to default
6
+25

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 = 5

This 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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.