Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
Dynamically adding columns is very silly. en.wikipedia.org/wiki/Entity–attribute–value_model is your best bet –  Phil Mar 12 at 2:03
    
I have considered EAV but was advised against. –  tntu Mar 12 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 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. –  tntu Mar 12 at 16:19

2 Answers 2

up vote 3 down vote accepted

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:

share|improve this answer
    
So you agree schema change can be a good solution if done properly? –  tntu Mar 12 at 18:56
1  
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 at 20:11

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:

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

share|improve this answer
    
So, how is the integrity achieved with XML or JSON columns? –  ypercube Mar 12 at 17:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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