Flexible forms and database structure

Question

I was instructed to create an application that allows administrators to change the contents of the user's input form (i.e. add arbitrary fields), the contents of which are stored in the database. Change your mind about the variables of the Think Modx / Wordpress / Expression Engine template.

The approach that I considered is to implement specific tables where the specification is consistent (i.e. user profiles, user content, etc.) and some common field data tables (i.e. text, logical) for storing non-specific values. Forms (and model fields) will be generated by first querying the tables and retrieving the appropriate columns, although I have not thought about how to configure validation yet.

I looked at this issue, and it seems to indicate an approach like EAV, which, it seems to me from my brief research, may be a greater burden than the blessings that flexibility will bring him.

I read a few posts here that suggest this is a dangerous route:

How to create a common database, the layout of which may change over time?

Dynamic database schema

I would appreciate some advice on this subject if anyone gives the opportunity

considers

SWK

Answer 1

I created a very large EVA database years ago (PHP w / PostgreSQL). It turned out fine, but it was a big project ($$$). All forms were fully dynamic, with a version of the form / field, publishing workflows, matching dynamic reporting, etc.

The basics of EVA are simple enough. Getting data is not difficult. But formal versioning and reporting ... you can spend years on the right decision.

If I did it again today, I would learn to use one of the new NoSQL solutions ( http://en.wikipedia.org/wiki/NoSQL#Document_store ). I would like to create a DTO style class that could be passed to the form generator. Modifying a form will actually change the DTO. Then I will persist in the DTO in the document / object database.

In addition, as you build your alpha solution, consider how to enable test cases that cover versioning and reporting needs.

Here is an example of what I mean: a simple "Request a Question Form".

• Original (version 1): has first, last, question
• Add Email Field (Version 2): First, Last, Email, Question
• Someone changes their mind about email: (version 3): First, last, question
• A new marketing guy comes in and changes it: (version 4): Full name, email address, question

Now you need to create a report (csv). Everything is getting complicated. How do you do this?

We solved this problem with a field-level version with links to their previous versions. In addition, the reporting system required the end user to compile a definition of report data sources before launch. (binding report fields to data fields, etc.).

However, with a DB document, I would suggest that you can do it differently. I believe that a new database, like CouchDB (others?), Has a mechanism built in to solve these problems.

Good luck

Answer 2

When developing user profiles in my last webapp, I chose the Key / Value approach. This is what my database project looks like:

Users table with fixed columns: id login name regdate 

User table associated with the profile table (HasMany user profile).

 Profiles table with different data: user_id field value 

Thus, the user can add an additional field any to his profile. For instance:

 user_id = 1 field = 'Facebook' value = 'http://facebook.com/...' 

and

 user_id = 1 field = 'Stackoverflow' value = 'http://stackoverflow.com/user/...' 

etc.

Answer 3

Depending on your needs, you might not even want to raise the form fields to the "DB fields" level. Instead, you can serialize these fields in a (essentially) dynamic block and store it in the database. This is NOT recommended if you have people who need to request these dynamic fields outside your application (i.e. database design is part of a broader public contract with integrated systems), but if you just use the application to easily search for these dynamic fields or if any aggregation / search capabilities within the fields are negligible, I would consider it (currently these capabilities are for the CPU). I used the template many times, and so far I have never had to refactor. (however, I can understand the case when you might need it).