slightly OT - forms in database

[t...@timothyarnold.co.uk]

Hi

Sorry for the slightly OT question. Im using CI or Cakephp so only slightly ..:)

I'm looking to develop a number of forms for a checklist type requirement. Each form will have a number of yes/no and free field text entries. No two forms will be the same.

Generally we will not have a requirement to search on the answers as its only really for historical purpose

Q1: is it OK to store this as json or seralize in a database field in a row? As we don't need to search and don't want to create many empty columns?

Is there another option? What about storing the form design and answers in an XML file?

The other question is - do you manually build the form design in PHP or has anyone used a database driven form builder before?

Thanks for looking and hopefully responding
Tim

[J.O. Aho]

On 08/02/15 10:47, t...@timothyarnold.co.uk wrote:

>
> Q1: is it OK to store this as json or seralize in a database field in a row? As we don't need to search and don't want to create many empty columns?

Depends on the database IMHO, if you go for postgresql, then json would
be an acceptable data type

http://www.postgresql.org/docs/9.3/static/functions-json.html

> As we don't need to search and don't want to create many empty columns?

That's todays requirement, but that will most likely change one day and
then you want to be able to search without having to do a lot of full
text searching.

> The other question is - do you manually build the form design in PHP or has anyone used a database driven form builder before?

This depends much on how the form is used, if the form is more or less
static, then it's easier just hard code it, if it change a lot then make
it dynamic where you have ha blue print for each form.
For your case I would go for something dynamic where you can easily
create forms, have this easily administrated from a web interface that
you won't have to spend time on making new forms, but some low paid
intern can do those.


--

//Aho

[Denis McMahon]

On Sun, 08 Feb 2015 01:47:42 -0800, tim wrote:

> Q1: is it OK to store this as json or seralize in a database field in a
> row? As we don't need to search and don't want to create many empty
> columns?

I would suggest not. Your requirements may change.

> Is there another option? What about storing the form design and answers
> in an XML file?

What about a table:

formid | questionid | answer

Now you have no empty columns, and one answer per record.

> The other question is - do you manually build the form design in PHP or
> has anyone used a database driven form builder before?

I generally build the form manually in html.

--
Denis McMahon, denismf...@gmail.com

[Jerry Stuckle]

No, it would not be an appropriate way of storing the data (violates 1st
normal form). And as Denis noted, while you don't need to search NOW,
requirements may change in the future.

Please read up on database normalization; you should have at least two
tables. One would have form information and the other would have
question information.

And I don't see any advantage in storing in an XML file.

As for building the form design - I just do it in PHP. It doesn't take
that long for something so simple - and would take longer to modify
integrate generated code into a page to match the rest of your site.

But no matter how you do it, I can't stress how important it is to
validate every response coming from the client, ensuring each has the
appropriate type and a valid value.

--
==================
Remove the "x" from my email address
Jerry Stuckle
jstu...@attglobal.net
==================

[Matthew Carter]

It would add some technical debt for sure, but running queries against
flat text fields (like TEXT type in MySQL) is still ridiculously fast
even on a few hundred thousand rows for LIKE type queries.

For instance, if you stored the following JSON as part of the entry (JSON
snippet):

{..., "first_name":"Matt","last_name":"Carter",...}

you could run this query without a huge headache:

SELECT * FROM table WHERE json LIKE '%"first_name":"Matt"%' AND
json LIKE '%"last_name":"Carter"%';

Depending on data sanitation (or lack of), easily possible to get false
positives (although you'd json_decode and check in PHP after the query).

If the fields in each form varied widely (each requiring it's own DB
structure) and there is a tiny chance you'd actually query by per column
basis, I think a separate table for each form would add a *lot* of up
front time.


--
Matthew Carter (m...@ahungry.com)
http://ahungry.com

[Jerry Stuckle]

You obviously don't have the slightest idea as to what database
normalization is - or why you would use it.

And no one suggested a separate table for each form.

I suggest you go back to Database 101 and learn some basic concepts
before you embarrass yourself further.

[Denis McMahon]

On Sun, 08 Feb 2015 15:41:29 -0500, Matthew Carter wrote:

> It would add some technical debt for sure, but running queries against
> flat text fields (like TEXT type in MySQL) is still ridiculously fast
> even on a few hundred thousand rows for LIKE type queries.

What happens when your database starts reaching tens of billions of rows.

There's a reason we normalise databases.

--
Denis McMahon, denismf...@gmail.com

[Thomas 'PointedEars' Lahn]

Matthew Carter wrote:

> It would add some technical debt for sure, but running queries against
> flat text fields (like TEXT type in MySQL) is still ridiculously fast
> even on a few hundred thousand rows for LIKE type queries.

But the index key length of TEXT-type columns is limited in MySQL.

> For instance, if you stored the following JSON as part of the entry (JSON
> snippet):
>
> {..., "first_name":"Matt","last_name":"Carter",...}
>
> you could run this query without a huge headache:
>
> SELECT * FROM table WHERE json LIKE '%"first_name":"Matt"%' AND
> json LIKE '%"last_name":"Carter"%';

That is like trying to parse HTML with a single regular expression.
Not only is it very inefficient, and does not work reliably, it also can
fail horribly.

> Depending on data sanitation (or lack of), easily possible to get false
> positives (although you'd json_decode and check in PHP after the query).

Exactly. Which is why you should never serialize information that need to
be queried. If you need to store objects in a database and retrieve them by
their properties, then for goodness’ sake either put the data in separate
fully-indexable fields in an RDBMS, or use an object-oriented one, like
MongoDB.

--
PointedEars
Zend Certified PHP Engineer
Twitter: @PointedEars2
Please do not cc me. / Bitte keine Kopien per E-Mail.

[Matthew Carter]

Think thats going to happen here? If it was a project where they
anticipated that scale, I don't think they'd have someone asking how to
do it here.

Reality is that it's probably only going to have a few thousand forms
filled out max, if that, but what do I know.