• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Is it good practice to add remove table columns on the fly?

#1
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
Reply

#2
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.
Reply

#3
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
Practical guide to IgnitedCMS - Book coming soon
Reply

#4
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.
Reply

#5
(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.
Practical guide to IgnitedCMS - Book coming soon
Reply

#6
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/...reams_core
Reply

#7
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
Reply

#8
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!
Reply

#9
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
Reply

#10
(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
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.