Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.php > #14905 > unrolled thread

slightly OT - forms in database

Back to article view | Back to comp.lang.php

Contents

  slightly OT - forms in database tim@timothyarnold.co.uk - 2015-02-08 01:47 -0800
    Re: slightly OT - forms in database "J.O. Aho" <user@example.net> - 2015-02-08 11:17 +0100
    Re: slightly OT - forms in database Denis McMahon <denismfmcmahon@gmail.com> - 2015-02-08 13:33 +0000
    Re: slightly OT - forms in database Jerry Stuckle <jstucklex@attglobal.net> - 2015-02-08 09:02 -0500
      Re: slightly OT - forms in database Matthew Carter <m@ahungry.com> - 2015-02-08 15:41 -0500
        Re: slightly OT - forms in database Jerry Stuckle <jstucklex@attglobal.net> - 2015-02-08 16:09 -0500
        Re: slightly OT - forms in database Denis McMahon <denismfmcmahon@gmail.com> - 2015-02-08 21:53 +0000
          Re: slightly OT - forms in database Matthew Carter <m@ahungry.com> - 2015-02-08 21:39 -0500
        Re: slightly OT - forms in database Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2015-02-09 00:52 +0100

#14905 — slightly OT - forms in database

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

[toc] | [next] | [standalone]

#14906

On 08/02/15 10:47, tim@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

[toc] | [prev] | [next] | [standalone]

#14908

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, denismfmcmahon@gmail.com

[toc] | [prev] | [next] | [standalone]

#14909

On 2/8/2015 4:47 AM, tim@timothyarnold.co.uk wrote:
> 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
> 

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
jstucklex@attglobal.net
==================

[toc] | [prev] | [next] | [standalone]

#14911

Jerry Stuckle <jstucklex@attglobal.net> writes:

> On 2/8/2015 4:47 AM, tim@timothyarnold.co.uk wrote:
>> 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
>> 
>
> 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.

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

[toc] | [prev] | [next] | [standalone]

#14913

On 2/8/2015 3:41 PM, Matthew Carter wrote:
> Jerry Stuckle <jstucklex@attglobal.net> writes:
> 
>> On 2/8/2015 4:47 AM, tim@timothyarnold.co.uk wrote:
>>> 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
>>>
>>
>> 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.
> 
> 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.
> 
> 

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.

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

[toc] | [prev] | [next] | [standalone]

#14915

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, denismfmcmahon@gmail.com

[toc] | [prev] | [next] | [standalone]

#14925

Denis McMahon <denismfmcmahon@gmail.com> writes:

> 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.

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.

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

[toc] | [prev] | [next] | [standalone]

#14922

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.

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.php

csiph-web