Storing and presenting custom table data

Question

I have a web site upon which I want to provide users the ability to create tables of data. These tables will come from a predefined list of table definitions of which I will be adding to myself. This list will likely grown into the hundreds. The table definitions will be created as Java classes. They will define how many columns there are and their data types (string, numerical, boolean, pick from list). It will also predefine some columns as automatically calculated form other columns. The number of rows will be limited but this could range from 10 to 10000. Additionally I will give users an option to create a custom table type where they set the number of rows and columns and the column types (string, numerical, boolean, pick from list - no formulas).

The tables must be sortable on certain columns (with secondary sort columns depending on the first). The data sorting will (probably) be done by the application rather than the database as it will include different logic in different table definitions. I might include a simple text search but not a filter. The table data may be updated but I expect this to be infrequent (days apart, mostly never).

What I can't decide on is the best way to store this information. I am using a relation db (postgres). Since each row will be made up of various different column types I can't store the data in separate database rows with relevant field types for each column.

On the web side I want the users to be able to view paged data. Users that don't have JavaScript enabled can put up with a basic table of paged data without sort options. Users with JavaScript enabled should have options to sort data.

Storage

Option 1

Create an XML doc containing all the data and store in one row of the database. This will be easy to read and write but means the entire web table must be loaded into memory even if only delivering a few rows to the browser.

Option 2

Create XML fragment for each row and store in separate database rows. Since data will be sorted by the application the order must either be calculated when the first page is displayed and then for further pages only the required rows returned, or when data is updated a predefined index is created for each sort option. The predefined index seems like the better option as it won't change often but will be read numerous times.

Delivery of data to the browser

Option 1

For JS enabled browsers get all the data using AJAX/JSON. The response would be cached on the browser and using JS populate a table with data. Further trips to the server shouldn't be required unless the data is updated (only detected when page is refresh using a last updated time embedded in the page). Subsequent visits to the page should use the cached copy of the data.

Option 2

Embed paged data in html. If JS is enabled then further updates using AJAX.

I don't want to use HTML 5 offline storage to support older browsers.

I think I have to go for both Option 2s to accommodate non JS browsers and to minimize the memory usage.

Are there any other possible solutions?

Answer 1

Alright, first of all, this sounds dangerously as if you're building an Inner platform: a poorly reimplemented user-visible version of the tool you're also using for developing the system. But if those really are the requirements, then fine, let's talk about how you're going to store such data.

There's no reason you should switch from a data base to XML to store user data unless there is a specific reason to do so. "each row will be made up of various different column types" isn't a good enough reason. You could still simply have a DB table whose rows store the user-defined tables and another DB table that stores the columns of the user-defined tables. In general, relational data bases can deal with complicated, even recursive user data quite well, even if the user data themselves constitute a data base of sorts.

But it cold be even easier. Why not map the user-defined tables to actual tables and their columns to actual columns? Most database engines allow you to set aside a schema, namespace etc. so that you can use any name in it without clashing with your own existing names used by your program, so you could simply implement creation of a user table as an actual CREATE and deletion as an actual DROP. There is nothing inherently dangerous about executing DROP statements as result of user input, if the affected tables were also created by the user in the first place.