1

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?

  • None of the above. Use good old database tables. – Tulains Córdova Aug 21 '14 at 13:13
2

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.

  • +100000 for the Inner platform alert. – Tulains Córdova Aug 21 '14 at 13:12
  • You have convinced me about dropping the XML approach. I spent a while thinking about the inner platform and you are right, that is what I am doing here. I initially thought you were referring to what the end users had access to as far as creating tables so was a little confused but a weekend and some beers has helped me understand. I had never thought about create creating a distinct table for each. It would make things much easier when displaying the data too. Thanks. – Goose Aug 26 '14 at 12:37

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.