Is it good practice to add remove table columns on the fly?

Hi guys,

I was just considering this, and wondering is it good practice to add or remove table columns on the fly. Can it ever be a good idea in practice?

I have a case where I am seriously considering this as the most efficient way of doing handling my data. Thoughts and ideas welcome.

Practical guide to IgnitedCMS - Book coming soon, www.ignitedcms.com

Can it ever be a good idea in practice? Maybe, there's always a rare, bizzare use case where it may make sense, given a few conditions.

But that's not the same question as "Is it a good practice?" ... No, it would be a horrible practice, because you were never meant to do that. If you think you need to do it, then probably you need something else instead, like PostgreSQL's JSON fields.

Hmm interesting, I will investigate further and let you know how I get on. I've never heard of postgre Json fields (heard of json of course) but it doesn't sound like something I want to get into

Practical guide to IgnitedCMS - Book coming soon, www.ignitedcms.com

I think it's an acceptable solution if you want to create a functionality to manage data structures similar to streams (PyroCMS) or channels (EE). I am using this method if I restricted to RDBMS (MySQL), if I can use anything then a NOSQL (for example MongoDB or Cassandra) is a more viable solution because they don't require to use a fix data structure, every record (object) might store data with different structure.

@Narf mentioned PostgreSQL's JSON fields, this is a hybrid solution which can be a good choice too.

I think it's an acceptable solution if you want to create a functionality to manage data structures similar to streams (PyroCMS) or channels (EE). I am using this method if I restricted to RDBMS (MySQL), if I can use anything then a NOSQL (for example MongoDB or Cassandra) is a more viable solution because they don't require to use a fix data structure, every record (object) might store data with different structure.

@Narf mentioned PostgreSQL's JSON fields, this is a hybrid solution which can be a good choice too.

Oddly enough that is exactly what I'm trying to do, replicate ee's channels. But I've never ever restructured a table on the fly or even thought about doing so, but the more I got thinking about it, the more it made sense, and the more easier it seems to dump my data without doing complex joins and worrying about content fields being changed.

I wanna stick with mysql as it is by far the most common db in use. This is meant for the masses.

Practical guide to IgnitedCMS - Book coming soon, www.ignitedcms.com

Then use the DBForge library with good conscience. The ol' big ones are doing the same too!

See the implementation in the old PyroCMS:
https://github.com/pyrocms/pyrocms/tree/...reams_core

Thanks that's what I was planning on doing, I guess I have to make sure the dbforge controller is on lock down as I don't want any exploits in the db.

PyroCMS are moving to laravel ain't they eugh! :/

Practical guide to IgnitedCMS - Book coming soon, www.ignitedcms.com

Yeah, they moved to Laravel, this decision almost ruined my company, because I wanted to stay with CodeIgniter - we made a lot PyroCMS based site in the last 4 years and also bought the Streams plugin for example - so I had to create my own CMS two years ago (currently it's not open-source). This was the best decision I could made, now I am not depending on anybody's solution and of course decision and kind!

there is no good practice or what it depend on what you needed, baseed on my experience i do delete and insert data form export and import database, nice disscusion

Hi guys,

I was just considering this, and wondering is it good practice to add or remove table columns on the fly. Can it ever be a good idea in practice?

I have a case where I am seriously considering this as the most efficient way of doing handling my data. Thoughts and ideas welcome.

From an RDBMS perspective is not a good practice and you would always try to avoid it. 
 
May be your table has a couple of rows but if the RDBMS has to add a column or drop a coulmn every time a user logs out and it is a big table it will cause performance issues on the DB.

In standard RDBMS this should not be done.

Hope this helps