Looking for a scalable, flexible and fast database design for 'Build your own form' style website - e.g Wufoo.
- User has only 1 Form they can build
- User can create their own fields or choose from 'standard' fields
- User's 1 Form has as many fields as the user wants
- Values can be the sibling of another value E.g A photo value could have name, location, width, height as sibling values
- User can submit their form a maximum of 5 times a day
- Value's Date is important
- Flexibility to report on values (for single user, across all users, 1 field, many fields) is very important -- data visualization (most will be chronologically based e.g. all photos for July 2009 for all users).
Table "field_user" - assign a field to a users form
weight - int - used to order the fields on the users form
creator_uid - int - the field 'creator'
label - varchar - e.g. Email
value_type - varchar - used to determine what field in the 'values' table will be filled in (e.g. if 'int' then values of this field will submit data into the values.type_int field - and all other .type_x fields will be NULL).
field_type - varchar - e.g. 'email' - used for special conditions e.g. validation rules
date - date
date_group - int - value 1-5 (user may submit max of 5 forms per day)
type_varchar - varchar
type_text - text
type_int - int
type_float - float
type_bool - bool
type_date - date
type_timestamp - timestamp
I understand that this approach will mean records in the 'Value' table will only have 1 piece of data with other .type_x fields containing NULL's... but from my understanding this design will be the 'fastest' solution (less queries, less join tables)