6

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?

  • 5
    What is your criteria for deciding what is "best?" Also, are you sure you're not creating an Inner Platform? – Robert Harvey Feb 21 '17 at 17:41
  • 1
    What you're describing is basically a "sparse matrix," i.e. a spreadsheet. It would help if we knew a little more about your use case, i.e. what is the actual problem you're trying to solve. – Robert Harvey Feb 21 '17 at 17:53
  • 1
    What will be in these tables? Is it necessary to store them in a format that can be queried via SQL? Or could just just serialize them into a blob and store that? – FrustratedWithFormsDesigner Feb 21 '17 at 17:59
  • 1
    Voting to close ... The actual problem isn't clear here. And as @RobertHarvey points out, there's no criteria here for what's "cleaner" or "better" ... Or what the real, underlying problem is. – svidgen Mar 24 '17 at 2:31
  • 1
    @svidgen: According to this author's profile, he hasn't visited the site again since he posted the question on Feb 21. – Robert Harvey Mar 24 '17 at 4:49
2

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.

  • Good answer, but pity the programmer who has to debug this structure/understand this structure. – Jon Raynor Mar 24 '17 at 16:07
  • Whats so complex about it? Debugging this seems a lot easier to me than implementing a table of cell values. There'll be a large class that handles the SQL generation and another large one that translates user input. Writing unit-tests for both shouldn't be that much of a task – marstato Mar 24 '17 at 16:45
  • Does this work with ten/hundres of thousands of small tables? – Jimmy T. Mar 26 '17 at 15:20
  • 1
    @JimmyT. Yes, it should because there are no constraints across the tables. The DB server can look at any of the user tables in Isolation. The information schema is gling to get big, of course, and that might cause issues. Thats why i suggest to put the user tables into a separate schema: it should reduce the impact it has on your regular tables. – marstato Mar 26 '17 at 15:24
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...

  • I was just going to suggest this. Or maybe SQL Server isn't the best choice here for data storage is the structure is not known at compile/run time. – Jon Raynor Mar 24 '17 at 16:05
0

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|

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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