User-created database structure: non-relational or relational databases?

Question

I want to have dynamic fields in my database records.

For example: I want to build an application for users to create their own forms.

A user could create the following forms:

Personal profile:

• Full Name
• Street
• Job
• Phone
  • Home
  • Work
  • Mobile
• Interests
  • Interest 1
  • Interest 2
  • Interest 3

Work:

• First name
• Last name
• Work
  • Department
    • Specialty 1
    • Specialty 2
  • Department
    • Specialty 1
    • Specialty 2

Countries:

• United states
  • States
    • New York
      • Cities
        • New York
        • Foo
    • Alabama
      • Cities
        • Bar
        • Baz

As you can see this is a very dynamical structure:

• No predefined number of fields
• No predefined field names
• User creates the structure of the database

So I wonder, what is the best database for this: relational (mysql/postgresql) or non-relational like mongodb/couchdb/cassandra or even xml databases like xindice?

And even if I choose non-relational databases for this, would it be smart to store security-critical information on it like customer and billing information?

I have heard people say that if your information require uniqueness then use relational database. "We don't want to risk to bill our customers twice". What problems on non-relational databases do they actually mean? Can't you store unique data in non-relational databases?

Another thing I was thinking about: Won't saving data in non-relational databases mean that I will have duplicated entries?

Consider this example:

Categories:

• Office

  • Applications
    • Textmate
      • Author : Foobar
      • Price : 120
    • Foo
      • Author : Foobar
      • Price : 120

• Office

  • Applications
    • Textmate
      • Author : Foobar
      • Price : 120
    • Bar
      • Author : Foobar
      • Price : 120

As you see there are situations for identical entries. How do non-relational databases handle these? Im so used to relational databases.

I sum up my questions:

• What type of database for user-created database structure?
• Are non-realtional databases for storing security critical information?
• How do non-realtional databases handle duplications?

Answer 1

I highly recommend you check out CouchDB for this.

1. You communicate with CouchDB using a straightforward REST API. In other words, it is "Made of the Web" rather than simply being a backend db like MongoDB and others. CouchDB can actually serve the forms and receive submissions since has a built-in web server.
2. Being a JSON document store it is well-suited for storing structured-yet-schemaless data. (Forms and their submissions are really documents and it makes more sense to model them this way, IMO.)
3. You could easily store a JSON document that describes each web form in the same "bucket" as the form submissions. (CouchDB can even parse form POSTs and turn them into JSON docs however you see fit. Having it automatically timestamp form submissions, for example, is simple.)
4. You could write what is known as a "_show" function to actually generate each form's html code within CouchDB. Also check out "_update" and validation functions.
5. It has the security features you would need.
6. Document conflicts can be identified easily. Even better, CouchDB automatically determines a "winning" version of the document but you will continue to have access to the "losing" document versions (until you tell CouchDB to compact the database, which removes old revisions.)
   • Regarding uniqueness: instead of having CouchDB generate unique doc _id's you'll want to assign an _id that truly represents a unique form submission. If each user is only allowed one submission per form then use something along these lines for each JSON document created from a form submission: submission:user:5:form:a3df2a712

Using CouchDB you can avoid the pain of dynamically creating unique tables for every form a user might create.