Database structure to save 'tables'

Question

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?

Answer 1

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:

CREATE TABLE usertable_4711_1;
ALTER TABLE usertable_4711_1 ADD COLUMN rowid INT NOT NULL AUTO INCREMENT;

ALTER TABLE usertable_4711_1 ADD COLUMN col1 VARCHAR(255);
...
ALTER TABLE usertable_4711_1 ADD COLUMN colX VARCHAR(255);

// inserts and updates go alike
INSERT INTO usertable_4711_1 (col1, col2, col3) VALUES ('data', 'data', 'data')

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.

Answer 2

XML columns

I'm suggesting this only as an alternative - it may or may not be your best option. Sql Server has support for XML columns with full XPath/XQuery on the XML document.

https://msdn.microsoft.com/en-us/library/hh403385.aspx

This will avoid the need to create separate schemas/user tables for the data. All information can be contained in one column. Just serialize the user's table to XML to insert and de-serialize to view.

Just an idea...

Answer 3

If you want 3 tables, you can do it like this:

tables - table_id, name, created_at, updated_at

rows - row_id, table_id, row_type, row_pos, created_at, updated_at

columns - column_id, table_id, row_id, column_pos, column_val, created_at, updated_at

row_type because a row can also have table headers. You can set it enum to indicate it's header or data.

Alternative and better way:

Tables table

|TableID|TableName|Rows|Colls|

TableData table

|TableDataId|TableID|Row|Column|Details|

Answer 4

Table TableID, Name

Column Column ID (primary key), TableID (foreign key), Position, Name, DataType, Unique (boolean). The combination {TableID, Position} must be unique.

Data ColumnID, RowID, Value. The combination {ColumnID, RowID} must be unique.