CodeIgniter Forums
Is it good practice to add remove table columns on the fly? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forum-5.html)
+--- Forum: Best Practices (https://forum.codeigniter.com/forum-12.html)
+--- Thread: Is it good practice to add remove table columns on the fly? (/thread-63232.html)

Pages: 1 2


Is it good practice to add remove table columns on the fly? - ignitedcms - 10-09-2015

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.


RE: Is it good practice to add remove table columns on the fly? - Narf - 10-09-2015

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.


RE: Is it good practice to add remove table columns on the fly? - ignitedcms - 10-09-2015

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 Big Grin


RE: Is it good practice to add remove table columns on the fly? - orionstar - 10-09-2015

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.


RE: Is it good practice to add remove table columns on the fly? - ignitedcms - 10-09-2015

(10-09-2015, 03:33 PM)orionstar Wrote: 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.


RE: Is it good practice to add remove table columns on the fly? - orionstar - 10-09-2015

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

See the implementation in the old PyroCMS:
https://github.com/pyrocms/pyrocms/tree/2.2/master/system/cms/modules/streams_core


RE: Is it good practice to add remove table columns on the fly? - ignitedcms - 10-09-2015

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! :/


RE: Is it good practice to add remove table columns on the fly? - orionstar - 10-09-2015

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!


RE: Is it good practice to add remove table columns on the fly? - freddy - 10-10-2015

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


RE: Is it good practice to add remove table columns on the fly? - estebanc - 10-14-2015

(10-09-2015, 02:23 PM)iamthwee Wrote: 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