db schema for custom forms

[diego]

hello there
im building a custom forms website like wufoo.com (you can check it out if you dont have idea what im talking about)

the users will create custom forms and then i need to save the form structure and future submissions in the database

this is what i have so far

table forms
- id
- name

table field_types
- id
- type (checkbox, input, select)

table fields
- id
- form_id
- field_type_id

this will show a really nice html form. but i really dont know how to store the submissions!
should i use one record for every filled field? that will be generate so many records and i really dont know how to perform searches in the future! ahhh

well... any ideas guys? thx!!

[Erick T. Barkhuis]

diego:

>hello there
>im building a custom forms website like wufoo.com (you can check it
>out if you dont have idea what im talking about)
>
>the users will create custom forms and then i need to save the form
>structure and future submissions in the database
>
>this is what i have so far
>
>table forms
>- id
>- name
>
>table field_types
>- id
>- type (checkbox, input, select)
>
>table fields
>- id
>- form_id
>- field_type_id
>
>this will show a really nice html form. but i really dont know how to
>store the submissions! should i use one record for every filled
>field?

Yes. A submission is a collection of filled fields of one form. You'd
have two more entities: submission and filledfield. Your ER-schema
would look like:

form
|1
-------------------
|n |n
fieldtype filledfield_--------_submission
|m |m n 1
--------------------
|1
field

A submission is a collection of filledfields, each of which belongs to
exactly 1 field of 1 form.

> that will be generate so many records and i really dont know
> how to perform searches in the future! ahhh

Where exactly do you see a problem? The number of filledfields? Why?
What type of search do you consider difficult?


--
Erick

[Erick T. Barkhuis]

Erick T. Barkhuis:

> form
> |1
> -------------------
> |n |n
> fieldtype filledfield_--------_submission
> |m |m n 1
> --------------------
> |1
> field

This is wrong (on the left side). I will delete this post (and the
previous one) in a minute and post a proper ER schema.



--
Erick

[Erick T. Barkhuis]

[This post is a corrected version of canceled
message <as1tl6...@mid.individual.net>]

diego:

>hello there

Hi Diego

>im building a custom forms website like wufoo.com

>the users will create custom forms and then i need to save the form
>structure and future submissions in the database

Great! So you would have all the submitted data.
What type of policy will you publish (and comply to) in order to make
the users trust you?

>
>this is what i have so far
>
>table forms
>- id
>- name
>
>table field_types
>- id
>- type (checkbox, input, select)
>
>table fields
>- id
>- form_id
>- field_type_id
>
>this will show a really nice html form. but i really dont know how to
>store the submissions! should i use one record for every filled
>field?

Yes. A submission is a collection of filled fields of one form. You'd
have two more entities: submission and filledfield. Your ER-schema
would look like:

form
|1

|n
field_------_filledfield_--------_submission
|m 1 n m 1
|1
fieldtype

A submission is a collection of filledfields, each of which belongs to

exactly 1 field (of 1 form, of 1 fieldtype). In this simple schema,
your application must make sure, that a submission only fills fields of
one form. The database won't do that, unless you add triggers that
prevent such cross-form submission.

> that will be generate so many records and i really dont know
> how to perform searches in the future! ahhh

[diego]

Erick, thank you for your reply! i have many more questions!

every single filled field will be stored in the database. so i will have the records vertically not horizontally right?

1. what about searching?
in a regular schema we could do something like
select * from submissions where form_id = n AND name LIKE '%maria%'

with this shcema that will be impossible

2. what about performance?
is a good practice to store records in that way?

what do you think about this?

table submissions
- id
- form_id

table filled_fields
- id
- form_id
- field_id
- value (data entered by the user)
- submissions_id

how can i loop through all records and its fields?

SELECT * FROM submissiones WHERE id = 1
....

so many questions.
and about your question... the legal department will handle it.


thx!!

diego

[Erick T. Barkhuis]

diego:

>every single filled field will be stored in the database. so i will
>have the records vertically not horizontally right?

Right

>
>1. what about searching?
>in a regular schema we could do something like
>select * from submissions where form_id = n AND name LIKE '%maria%'
>
>with this shcema that will be impossible

I am not sure what you consider a 'regular' schema. The one I gave is,
in my opinion, quite regular. Of course, you can't have a query like
the above.
If you gave me a result you would like to retrieve, I may give you the
query, based upon a database derived from the ER model I painted.

It would be something like

SELECT ffx.fieldcontents FROM filledfields ff
INNER JOIN fields f ON f.fieldid=ff.fieldid
INNER JOIN fx.fields fx ON fx.formid=f.formid
INNER JOIN ffx.filledfields ON ffx.fieldid=fx.fieldid
WHERE ff.fieldcontents LIKE "%maria%"
AND f.fieldname = "name"
ORDER BY fx.fieldname

Of course, you'd only need a handful of different queries, and you
would prepare each just once, so this looks worse than it is.

>2. what about performance?
>is a good practice to store records in that way?

Well, I'm still in the phase of setting up an ER model, not building
the database, so we're not "storing" anything, yet. Our first concern
is to have a normalized model and include all possible functionality
that you require.

>
>what do you think about this?
>
>table submissions
>- id
>- form_id

>table filled_fields
>- id
>- form_id
>- field_id
>- value (data entered by the user)
>- submissions_id

This would be redundant. If you have filled_fields.submissions_id, you
don't need form_id, because in your model, each submission is already
connected to only one form.
Also, if you have field_id, you don't need form_id, because each field
is only related to one form.

>
>how can i loop through all records and its fields?

In an RDBMS, you don't "loop through" data. Looping is something an
application would do. The database query returns a result set, which
you may transform to an array in your application and go from there. If
your application is a website or web-app, you may want your API to
return a json-object and use that to present the data.

>
>SELECT * FROM submissiones WHERE id = 1

What are you trying to retrieve here? All you would get are the values
of all attributes/fields of the submission (e.g. submission date, time
and IP address). If you want values from 'form', 'field' or
'filledfield', you need to join tables.


>....
>
>so many questions.

There are more important questions that have to be answered, first. For
instance:

- what do you want to happen, if a user deletes a field from a form,
after 100 submissions have been entered already? Do you want the
submitted values for that field to be destroyed?
If so, the user can't undo such action, which may be a problem (but it
would keep your database clean and simple). If not, then you need to be
able to switch field definitions to active/not-active and present forms
based upon the value of this switch. 'Deleted' fields would remain in
the database, and so would the submitted data.

- how do you want to map values to the field type? Do you want to store
date values as a mySql-DATE-field, for instance?
If so, what do you want to happen with the submitted data, if a user
changes the field type?

- do you need to know who created forms and fields? Do you need to know
where the forms are used (for instance for security reasons)? If so,
you need relevant entities in your ER model.


--
Erick

[diego]

Hello there.

im still working on this. reading a lot to find the best solution

i found that one of the ways is using EAV model (http://en.wikipedia.org/wiki/Entity-attribute-value_model) like Erik Said. but this approach is not the best because of searching into fields will be very complex

another option will be create two tables one for labels and one to store values with 50 fields (that will cover most of the cases i think) but with this option reordering fields will be impossible

anything else to say about this?
thx for the help!

diego



On Wednesday, April 3, 2013 1:37:31 AM UTC-3, diego wrote:

[Erick T. Barkhuis]

diego:

>i found that one of the ways is using EAV model
>(http://en.wikipedia.org/wiki/Entity-attribute-value_model) like Erik
>Said. but this approach is not the best because of searching into
>fields will be very complex

What do you consider "very complex"?
On April 4, I already gave you a query that would find all forms
containing "maria" in the "name" field. It would be really easy to use
that as a prepared statement for "give me all fields with their values
from all forms that contain $string1 in field $fieldname1"

>another option will be create two tables one for labels and one to
>store values with 50 fields (that will cover most of the cases i
>think) but with this option reordering fields will be impossible

You never mentioned anything about field order. Also, you never
mentioned something like labels.
Probably, there will be several other attributes in your dynamic form
creation system, which you haven't mentioned yet. That's why you need a
decent ER-schema of your business information first. Properly set up,
you can extend such model with additional attributes or even entities
and relationships.

>anything else to say about this?

Well, I asked a couple of questions in this thread, but you never
answered them. That makes things a bit difficult.
Before you can model your information, you need to _know_ your business
and goals, first. That's why I asked questions like:

- what do you want to happen if a form-owner changes his form after
several hundreds have been submitted?

- how do you want your dynamic forms to validate data? Do you want form
owners to include validation rules?

- do you need to know who created forms? Do you need to know who
submitted forms? Should anyone be able to 'un-submit' a form, and if
so, who?


--
Erick

"A quick question is one that was formulated quickly, thus greatly
increasing the time needed for getting a useful answer" - Yukka Korpela

[diego]

Hello Erick
i will try to explain exactly what i need ;)

do you know salesforce (woofoo was a bad example, sorry)? is a cloud based CRM
it lets you generate custom fields for customers attributes
is a shared environment so (obviously) is impossible to modify the database structure.
I read that they use EAV

some customers want to store name, last, emails..
other wants to store name, clientID, age, etc, etc

Asking your questions

YES i need to sort the data by any field!

if the customer deleted a "column" we are sorry. data will be lost

i will validate using javascript. but this is not a problem i think

by labels i mean. add a name to a "column"
if the customer wants to store the "company phone"
that will be the name "company_form"

for me a "classic" query will be

SELECT id, name, email, phone FROM
contacts
WHERE name LIKE '%maria%'
ORDER BY id desc

as you can see one record = 1 database row
with the EAV model like we saw. we will have one database row for each "column"

SELECT ffx.fieldcontents FROM filledfields ff
INNER JOIN fields f ON f.fieldid=ff.fieldid
INNER JOIN fx.fields fx ON fx.formid=f.formid
INNER JOIN ffx.filledfields ON ffx.fieldid=fx.fieldid
WHERE ff.fieldcontents LIKE "%maria%"
AND f.fieldname = "name"
ORDER BY fx.fieldname

thanx for your pacience!!!

diego



On Wednesday, April 3, 2013 1:37:31 AM UTC-3, diego wrote:

[Erick T. Barkhuis]

Hi Diego!

diego:

>Asking your questions
>
>YES i need to sort the data by any field!

Ehrm...this is getting really confusing, now.

You said: "...but with this option reordering fields will be
impossible"
And I replied: "You never mentioned anything about field order."

Now, all of a sudden, you mention "sorting data by any field", which is
something completely different from the field order on a custom form.

>i will validate using javascript. but this is not a problem i think

Are we talking about the same thing, here?

Suppose, a form owner defines his custom form as follows:
- field-1: label "Article", fieldname "myArticle", type VARCHAR(100)
- field-2: label "Price", fieldname "myPrice", type DECIMAL(7,2)
- field-3: label "from Age", fieldname "myAgeLimit", type INT(3)
[well, just suppose he does]
Now, suppose he wants to include a rule, which has to be validated
whenever a visitor submits data into the form. The rule is:
if myPrice>1000 and myAgeLimit<21
then show_error_message "no expensiv articles for minors allowed"

I wondered if you want to have form owners define such validation
rules. If so, you need to extend your data model, so it can hold such
validations.
--> Be aware that it is quite difficult to define "validation rules"
as data, to be stored in a database.

>by labels i mean. add a name to a "column"
>if the customer wants to store the "company phone"
>that will be the name "company_form"

May I assume that this is a typo?


--
Erick

[diego]

Hi Erick,
forget the validations i think with a "required" column we will be OK
extended validation is interesting but like you said it is complex.

- the data will be stored in VARCHAR (255)

- Yes. searching and sorting is important for all the custom fields

- by label i mean name of the field to be displayed. "my company name", "marital status" ,etc

On Wednesday, April 3, 2013 1:37:31 AM UTC-3, diego wrote: