0

I feel this is so basic, but it just seems too easy and obvious and why didn't someone suggest this 10 years ago. And I know there a lot of much brighter people than me in the software dev business...

So I've been asked to do a quick-n-dirty job (2 days) for a web page app - collect a form, retrieve and duplicate form as text not form elements. All via browser. Tool stack is limited to basic LAMP setup and whatever the web guys/gals can squeeze in via jquery, etc.

No problem. I'd normally set up a db table with a field for each element captured, appropriate data types, lengths, etc. parse and sanitize the input, insert to db with prepared statements, etc. then retrieve and re-display via a template and tokens. Basic CRUD stuff, have quiet a few web apps that do all sorts of stuff like that already, no problem.

But the issue is the form that needs to be filled out has areas where multiple sub-records (what schools attended - school, dates, degrees if any awarded - could be one, could be seven). And there are quite a few other forms that could use the same process. And the web guys/gals want to be able to do this without me being so involved (I agree - programming is only about 30% of my job).

So I'm thinking that each form has something in common with all the others - first name, last name, and email address. Anything else after that is up in the air, the only guarantee is no file uploads.

Would I be asking for trouble if I were to set up a simple generic processing script that submitted to a table, laid out as

pk | webform name | first name | last name | email address | submission timestamp | complete form as JSON string

??

Doing something like this seems so simple and trivial... I have to wonder...

Set up the insert, and for retrieves an almost-API for the web guys/gals to grab the data with, simply present them the JSON string. No worries about a second/third/Nth table with relationships back to the demographic stuff, having to create databases and tables for each one individually, etc.

Am I setting myself up for trouble for data storage this way? It seems too easy. What am I missing?

EDIT - the follow up

And now, a few months later... I went with storing JSON string to represent the complete form, but only as a one-of quick and dirty fix. Re-creating the form with the users content for edits is trivial and working well.

Unfortunately, stuck on very old versions of software due to ITS (they do infrastructure and ERP stuff, my group is separate and does all "regular" web) and changing to something like PHP 5.6 or even 7.x is out at the moment, and just forget about MongoDB or any other server side stuff.

Am working with the front end folks and trying to come up with a real solution but things are on hold as the semester starts (we all go nutz doing other things for a month) so any other solutions presented would be great.

  • 1
    I've not used it personally, but MySQL has JSON support now. If the data is truly unstructured (or varies greatly) that might be something to consider. – GrandmasterB Jun 20 '18 at 2:43
  • 1
    @GrandmasterB thanks. Stuck on PHP 5.3 and I don't even want to ask how old the mysql version is... – ivanivan Jun 20 '18 at 2:57
  • I dont think this deserves a full on answer, but have you considered storing forms as rows, and having a simple list of row IDs to reconnect them ? – TurtleKwitty Aug 9 '18 at 17:34
1

It highly depends on how you intend to use the data.

  • Your data seems to be structured
  • Some fields consist of related classes or sub classes (schools, degrees)

If the data is used in any other way than displaying, it might be worth not creating technical debt from day one. Consider upgrading your stack, normalising your data and taking the time to make proper design decisions. Start with functionally describing what this form is exactly (think domain object).

Having said that, there are a few options to really hack-and-slash it:

  • Storing the unknown fields as json in JSON data type (MySQL >= 5.7.8) or TEXT type
  • Firing up a document database like MongoDB which is intended for this type of storage.
1

If I understand the problem correctly, the standard way to deal with this kind of thing in a relational DB is to create a table with 4 columns (or more, if needed.): primary key, parent key, field name, field value. Where parent key is a foreign key to the main record and field name is a string-like column of moderate size and field value is a string-like column of large size.

Then each record you create will have the standard form fields and 0-n rows in the subordinate table for the non-standard fields.

Storing the extra data as one big string is feasible but someone (e.g. the client) will need to keep track of the format of the field. Inevitably, the client will evolve and the data in this field will vary over time. It's also more difficult to do queries against this kind of field than if you break it out. You essentially lose a lot of the value of a relational DB by putting unstructured data in it. If you don't really need the relational features, then it may be time to consider moving to a simpler storage mechanism.

  • What queries does a key-value subtable facilitate? (Asking for information; not rhetorical) – Lawrence Sep 8 '18 at 6:07
  • @Lawrence Too many to enumerate. You can do pretty much anything you can do with defined columns. Some things are cleaner such as finding all there records that have a field (or set of fields) or all that don't. The downsides are that there may be some performance penalty and you can't enforce types or constraints as easily. If there's a particular type of query you aren't sure about, I can elaborate on specifics. – JimmyJames Sep 9 '18 at 14:09
  • Hmm, if the subtable holds values of different formats, I don’t see how any database operation can be optimised. E.g. first set: 5 key-value pairs, all integers; next set: 2 key-value pairs, both strings; third set: 1 key-value pair, floating point. You’d end up with all the overheads of maintaining the subtable but I don’t see any optimisations possible on the OP’s generic database code, whether at insertion or retrieval. – Lawrence Sep 9 '18 at 14:29
  • @Lawrence If you put it in as a JSON object, you need to parse the entire value every time just to know what keys are there. The general approach is that you have a subtable and all the values are just char/string. JSON is just a string anyway so that's not a downside. You haven't lost with respect to that. – JimmyJames Sep 11 '18 at 14:35

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.