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

Groups > comp.lang.php > #14911

Re: slightly OT - forms in database

Show all headers | View raw

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

Back to comp.lang.php | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

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

csiph-web