I'm working on a project in which a user can create tables to save some data. What would be the best way to implememt this? Right now I'm doing it like this:
Table 'tables' – contains stuff the user setup like a name.
Table 'rows' – contains an id and relation to 'tables'.
Tables 'cells' – contains an id, value, position and relation to 'rows''.
Is there a cleaner or better way to do this?
Best Answer
Since @RobertHarvey mentioned the Inner Platform:
Why not use the RDBMses capability to maintain a table? Create a separate schema to put your users tables in. For each user table, create a DB table called something like
usertable_userId_counter
.Define all the rows and columns within that table. You can also define an increment counter that works similar to the ROW_ID in SQLite:
If you want your users to be able to put more metadata (or more complex constraints) on the table, you could also create a table
usertable_userId_counter_meta
that holds the metadata.I think that this would yield much better performance than trying to recreate the thing with SQL. If you use a proper query-builder you can safely handle the user defined tables.