4

I have been tasked with creating an application that allows administrators to alter the content of the user input form (i.e. add arbitrary fields) - the contents of which get stored in a database. Think Modx/Wordpress/Expression Engine template variables.

The approach I've been looking at is implementing concrete tables where the specification is consistent (i.e. user profiles, user content etc) and some generic field data tables (i.e. text, boolean) to store non-specific values. Forms (and model fields) would be generated by first querying the tables and retrieving the relevant columns - although I've yet to think about how I would setup validation.

I've taken a look at this problem, and it seems to be indicating an EAV type approach - which, from my brief research - looks like it could be a greater burden than the blessings it's flexibility would bring.

I've read a couple of posts here, however, which suggest this is a dangerous route:

How to design a generic database whose layout may change over time?

Dynamic Database Schema

I'd appreciate some advice on this matter if anyone has some to give

regards

SWK

Community
  • 1
  • 1
sunwukung
  • 2,815
  • 3
  • 41
  • 56

3 Answers3

3

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

EVA basics are easy enough. Getting data in is not the hard part. But form versioning and reporting....you can spend years getting it right.

If I was doing it again today, I would research using one of the newer NoSQL solutions ( http://en.wikipedia.org/wiki/NoSQL#Document_store ). I'd thing about creating a DTO style class that could be passed to a form generator. "Modifying" the form, would actually be modifying the DTO. Then I would persist that DTO into a document/object database.

Also, as you are building your alpha solution, think of how to solve test cases that encompass versioning and reporting needs.

Here is an example of what I mean: A simple "Ask Question form".

  • Original (version 1): has First,Last,Question
  • Add email field(Version 2): First,Last,Email,Question
  • Somebody changes their mind about email: (version 3): First,Last,Question
  • New marketing guy comes in and changes it: (version 4): Full Name,Email,Question

Now, you need to generate a report (csv). Things get tricky. How do you do it?

We solved this problem with field level versioning with references to their previous versions. Plus the reporting system required the end user to assemble the definition of the report data sources before running. (binding report fields to data fields, etc).

However with the document DB's I'd imagine you can do it differently. I believe the new DB's like CouchDB (others??) have mechanism built in for handling these issues.

Good luck!!

Lance Rushing
  • 7,540
  • 4
  • 29
  • 34
1

When developing user profiles in my last webapp, I've chosen Key/Value table approach. Here's how my DB design looks:

Users table with fixed columns:
id
login
name
regdate

Users table linked with Profiles table (User HasMany Profile).

Profiles table with different data:
user_id
field
value

This way user can add any additional field to his profile. For example:

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

and

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

and so on..

Sergei
  • 2,747
  • 1
  • 17
  • 16
0

Depending on your needs, it might not be worth even raising the form fields to the "DB fields" level. You could instead serialize these fields in (what is essentially a) dynamic blob and store it in the DB. This is NOT recommended if you have folks who need to query these dynamic fields outside of your app (i.e., the DB design is part of a larger public contract with integrated systems), but if you're just using the app to simply to persist these dynamic fields or if any aggregation/search capabilities within the fields are minor, then I would consider it (esp given CPU capabilities these days). I have used the pattern many times and I have - thus far - never had to refactor. (however, I can understand a case where you might need to).

kvista
  • 5,039
  • 1
  • 23
  • 25