Storing Dynamic Properties Of Objects In Sql

I am trying to store 'dynamic' properties about objects in SQL. As an example, let's say I have a table called objs that has two columns (id, name). Now some users may want to stor

Solution 1:

This has been discussed repeatedly before:

• This DBA.stackexchange.com post
• Dynamic table columns based on user preferences
• Should I place EAV values in a datatype table?
• How to represent many similar attributes of an entity in a database?
• Database design - should I use 30 columns or 1 column with all data in form of JSON/XML?
• What is the maximum number of columns in a PostgreSQL select query

The short version: EAV has its place, but it's often better to use json, XML, or hstore. PostgreSQL 9.4's enhanced json will probably become the most attractive choice, as it combines the advantages of json and hstore.

Solution 2:

First, you should start with a proper database schema (using standard data model patterns) so you can avoid this as much as possible.

Martin Fowler recommends using either a serialized LOB (such as JSON or XML), or allowing the user to edit their own database schema (which is my preferred method):

http://martinfowler.com/bliki/UserDefinedField.html

Bill Karwin has a post somewhere on creating a second table to index values in the blob field, but I can't find it right now. Will post when I do.