+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: Best Practices (https://forum.codeigniter.com/forumdisplay.php?fid=12)
+--- Thread: Is it good practice to add remove table columns on the fly? (/showthread.php?tid=63232)
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
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!
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