php - Dynamic Table Generation

Question

First let me describe my situation so that you might be able to help me better. There are two parts.

1: I have a program that runs and analyzes a bunch of files. It generates a "report" that will later be fed into a website for DB storage and viewing. This report can contain pretty much any type of data, as the users can query pretty much anything. I left it very open ended.

2: The website parses through this report, adds an entry for things that are common. But also creates a new table for any new data it finds. It also stores a mapping from report_id to all of these dynamically created tables. For example if in the report someone wanted to calculate Standard Deviation, and this made sense for this report, than there would be a STD table.

Right now this site is written in PHP, and looks kind of messy. Is there a better way to do this PHP. Also, I'm considering reworking this in Rails because organization sake. Is there a better way in rails, "method_missing?".

I am not very skilled at building websites, and amateurish at DB, so please be kind.

Thanks Eric

Answer 1

Looks like that you have unstructured, or at best semi-structured data. Classic relational DB tables are not ideal for this, unless you use XML as storage. You may consider coming up with an intermediate report-definition language and then storing that in a DB, usually as XML. MS SQL server, Oracle, and DB2 support storage and query of XML data.

After some thinking..
You could also look into the observation pattern and see if you could adapt it to this example. Although the pattern is OO, it can be done in SQL.
It is a bit long explanation, but I have published a simplified model here; hope you find this useful.