I wish to have dynamic fields during my database records.

For instance: I wish to build a credit card applicatoin for customers to produce their very own forms.

A person could produce the following forms:

Personal profile:

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

Work:

  • Name
  • Surname
  • Work
    • Department
      • Niche 1
      • Niche 2
    • Department
      • Niche 1
      • Niche 2

Nations:

  • U . s . states
    • States
      • New You are able to
        • Metropolitan areas
          • New You are able to
          • Foo
      • Alabama
        • Metropolitan areas
          • Bar
          • Baz

As you can tell this can be a very dynamical structure:

  • No predefined quantity of fields
  • No predefined area names
  • User produces the dwelling from the database

And So I question, what's the best database with this: relational (mysql/postgresql) or non-relational like mongodb/couchdb/cassandra as well as xml databases like xindice?

And even when I select non-relational databases with this, will it be wise to keep security-information onto it like customer and billing information?

I've heard people state that in case your information require originality then use relational database. "We don't wish to risk to bill our clients two times". What problems on non-relational databases will they really mean? Can't you store unique data in non-relational databases?

Another factor I believed about: Will not saving data in non-relational databases imply that I'll have copied records?

Think about this example:

Groups:

  • Office

    • Programs
      • Textmate
        • Author : Foobar
        • Cost : 120
      • Foo
        • Author : Foobar
        • Cost : 120
  • Office

    • Programs
      • Textmate
        • Author : Foobar
        • Cost : 120
      • Bar
        • Author : Foobar
        • Cost : 120

As you can see you will find situations for identical records. How can non-relational databases handle these? Im accustomed to relational databases.

I summarize my questions:

  • Which kind of database for user-produced database structure?
  • Are non-realtional databases for storing security information?
  • How can non-realtional databases handle duplications?

In case your data fits the relational model pretty much, but you have to store some dynamically formatted data that is not enormous, then you'll most likely be best storing JSON, XML, or similar right into a column. Even though you lose some benefits of first-class SQL typing using this method (indexing, foreign key constraint checking, type checking, etc.), it is good for storing dynamically-structured documents whenever your queries don't care much regarding their internals.

If you are thinking about storing mostly relational data with a little JSON/XML/etc., I suggest searching to PostgreSQL. PostgreSQL comes with an XML data type, however i don't recommend utilizing it since i have hate XML :P . No one's preventing you against storing JSON inside a TEXT area, but PostgreSQL will quickly possess a JSON data type with supporting functions. The hstore contrib module offers an efficient method to store key/value pairs, as well as provides full-text index support.

Although pushing JSON or similar right into a SQL database column flies when confronted with the relational model, you are usually best doing the work anyway (when it seems sensible!). Otherwise, you need to explain the whole schema of the application towards the database, resulting in many SQL and database mapping code that actually does not do anything whatsoever.