I am working on building a sort of a cms for a particular project. Nothing that will have a large volume of records in any table. Within 6 digits max.

I was thinking to create the default schema for all tables but then allow the editors to add custom fields to certain tables by adding fields to the schema. Obviously there would be the option to also drop fields.

I'm not an expert but neither a noob and I am sure I can write the proper codes to not get things mixed up and make a mess of it.

But still would this have any impact on the data integrity if changes are made on populated tables?

What are the hazards I could come across?

  • 2
    Dynamically adding columns is very silly. en.wikipedia.org/wiki/Entity–attribute–value_model is your best bet – Philᵀᴹ Mar 12 '14 at 2:03
  • I have considered EAV but was advised against. – transilvlad Mar 12 '14 at 8:11
  • 1
    EAV is a bad thing when you have better choices, it doesn't sound like you do here. – rfusca Mar 12 '14 at 16:15
  • Why is changing the schema a bad idea? If the data will never be to big to cause huge load and take very long? I need concrete reasons, there are plenty reasons pro and con EAV. I do not like the cons as they would give me to much of a problem. – transilvlad Mar 12 '14 at 16:19

I did a presentation about the various solutions to support custom fields:

The most straightforward option for greatest compatibility with SQL, including constraints and data types, is just to add columns as needed. But I would caution to not let users do this, instead let them submit a request and the DBA can perform the operation. A good tool to reduce impact is pt-online-schema-change.

Other solutions include:

  • So you agree schema change can be a good solution if done properly? – transilvlad Mar 12 '14 at 18:56
  • 2
    Yes, the best implementation for custom schema extensions that I remember seeing is in Bugzilla, the old Perl bug-tracking app. You can add columns, and also add code in a separate directory to work with those columns. – Bill Karwin Mar 12 '14 at 20:11
  • Like the comparison from hierarchy types (in your book) i like especially the "summary comparison" table – ThaDafinser Jun 9 '15 at 11:10

Don't use EAV. There's no integrity and it's hard to query.

Your best bet is either to simply change the database schema, or to use LOB fields, such as XML or JSON. These are what Martin Fowler recommends:


  • 1
    So, how is the integrity achieved with XML or JSON columns? – ypercubeᵀᴹ Mar 12 '14 at 17:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.