Dynamically change schema to accommodate custom fields!

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?

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

• Extensible Data Modeling with MySQL.

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:

• EAV (but before you try it please read the article Bad CaRMa, and my blog post EAV FAIL)

• Class table inheritance

• Serialized LOB with Inverted Indexes

• NoSQL document-store solutions

StackExchange Database Administrators Q#60702, answer score: 8