In the last post, I discussed how user defined fields can be stored in predefined columns in tables. There are other ways to store user defined fields in a database.

I have seen some applications store user defined fields in custom BLOB or CLOB columns as objects or structured text that store the data in a serialized fashon. The problem with doing that is that the fields will only be available through the application and will be hard to query through an RDBMS. This may be desired by the application designer, though, if they want to actively deter manipulation of the database directly (security through obscurity). Also, fields will not be properly indexed.

A new way to store fields, where they are indexed, is to use JSON columns and other structured storage columns within databases that support indexing. Here's an example from Oracle on their support for JSON columns. Database vendor support may vary and it will depend on the back end RDBMS you choose to use.

You can also go in a different direction and use a document database, or NoSQL database, such as MongoDB. In this case, each field is optional in any record as everything is stored in name value pairs. It will depend on how many user defined fields you need and how structured you want your data to be. If you have a lot of relational tables in your schema, this may not work well.

This is a short post, but the next post will lead to a discussion of how to maintain user defined fields in dynamically created columns in an RDBMS and a storage architecture for those tables and fields. Later articles will go over a software architecture that can support any type of storage by changing out the entity model and the support and maintenance of those user defined fields through that architecture.