C# – Database structure to save ‘tables’

cdatabasesqlsql server

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:

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.

Related Topic