5

The user wants to add new fields in UI dynamically. This new field should get stored in database and they should be allowed to perform CRUD on it.

Now I can do this by specifying a XML but I wanted a better way where these new columns are searchable. Also the idea of firing ALTER statement and adding a new column seems wrong.

Can anyone help me with a design pattern on database server side of how to solve this problem?

Mehdi Karamosly
  • 5,388
  • 2
  • 32
  • 50
Rohit Harshvardhan
  • 85
  • 1
  • 4
  • Why does the idea of firing an ALTER statement seem wrong? – Babak Naffas Dec 20 '10 at 19:59
  • 2
    What RDBMS? What **exactly** is the user asking to do? "Adding fields" does not necessarily mean you need to create new fields in your database. – JNK Dec 20 '10 at 20:00

3 Answers3

9

This can be approached using a key value system. You create a table with the primary key column(s) of the table you want to annotate, a column for the name of the attribute, and a column for its value. When you user wants to add an attribute (say height) to the record of person 123 you add a row to the new table with the values (123, 'HEIGHT', '140.5').

In general you cast the values to TEXT for storage but if you know all the attributes will be numeric you can choose a different type for the value column. You can also (not recommended) use several different value columns depending on the type of the data.

This technique has the advantage that you don't need to modify the database structure to add new attributes and attributes are only stored for those records that have them. The disadvantage is that querying is not as straightforward as if the columns were all in the main data table.

Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
  • I think this seems like the best approach I have seen till now. Thanks a lot. I will be trying this out. – Rohit Harshvardhan Dec 21 '10 at 03:09
  • Best of luck. My suggestion is not the only solution, nor is it perfect, but it has flexibility and avoids the issue you correctly identify as a problem -- having the user modify the database structure. – Larry Lustig Dec 21 '10 at 03:10
  • IT Is so good, but how can we search a person. for example who is person with height=140.5 and weight=56? – saeed arash Jan 30 '13 at 06:40
  • @saeed: There are several ways. You could join the attributes table to the person table twice (once for the height attribute and once for the weight attribute), you can use a couple of WHERE EXISTS clauses, you can perform a couple of singleton SELECTs inside your WHERE clause, etc. – Larry Lustig Jan 30 '13 at 13:59
  • For example, the database of Magento e-commerce software uses this sort of entity-attribute-value (EAV) model with three important tables: one defining column names, one for `VARCHAR` values, and one for `INTEGER` values. – Damian Yerrick Mar 07 '18 at 18:59
  • Yea, but If you want to use BOOLEAN, you have to store a whole byte instead of a bit. – Danon Oct 15 '18 at 12:44
1

There is no reason why a qualified business user should not be allowed to add a column to a table. It is less likely to cause a problem than just about anything else you can imagine including adding a new row to a table or changing. the value of a data element.

Using either of the methods described above do not avoid any risk; they are simply throwbacks to COBOL filler fields or unnecessary embellishments of the database function. The result can still be unnormalized and inaccurate.

These same business persons add columns to spreadsheets and tables to Word documents without DBAs getting in their way.

Of course, just adding the column is the smallest part of getting an information system to work, but it is often the case that it is perceived to be an almost insurmountable barrier. It is in fact 5 min worth of work assuming you know where to put it. Adding a column to the proper table with the proper datatype is easy to do, easy to use, and has the best chance of encouraging data quality.

Bob Schmidt
  • 11
  • 1
0

Find out what the maximum number of user-added fields will be and add them before hand. For example 'User1', 'User2', 'User3', 'User4'...etc. You can then enable the fields on the UI based on some configurable settings.

k rey
  • 611
  • 4
  • 11