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?

Originally asked by: ajsie on Stack Overflow