Page 1 of 2

Dynamic schema tables

Posted: Thu Feb 01, 2007 11:51 pm
by alex.barylski
Say you want to allow an end user the ability to add/remove fields from a table, how would you go about implementing such a thing?

I was hoping to use something like OsisForms for the front-end but I might just investigate a custom FORM library.

I wonder if you could write a generic CRUD class which handled all this based on the schema...then somehow generated INPUT fields, CRUD SQL, etc...from the schema as well...hmmm...interesting...

Obviously this is possible and I have seen it done before and I have investigated my own techniques, but I'm curious to hear what you all may have to say about it???

Posted: Fri Feb 02, 2007 12:45 am
by wei
may be something like Active Record + Scaffold (to borrow from ruby on rails terminology).

Posted: Fri Feb 02, 2007 1:25 am
by alex.barylski
wei wrote:may be something like Active Record + Scaffold (to borrow from ruby on rails terminology).
Here's the thing...the client using a simple interface says "OK I want to add a Middle Name field to my contacts DB".

Using Active Record...would require the object (that mapped the contacts table) to be updated which would then update the schema - if my understanding correct...?

Re: Dynamic schema tables

Posted: Fri Feb 02, 2007 2:45 am
by Christopher
Hockey wrote:Say you want to allow an end user the ability to add/remove fields from a table, how would you go about implementing such a thing?
You could store them in a second table containing a key relating back to the main record, the field name and the field value.
Hockey wrote:Here's the thing...the client using a simple interface says "OK I want to add a Middle Name field to my contacts DB".
Maybe create an interface that allows the client to ALTER the database schema. It shouldn't be too difficult to add and remove fields. Then your Active Record implementation could use DESCRIBE to get the actual schema.

Posted: Fri Feb 02, 2007 2:55 am
by Kieran Huggins
If you can figure out my saving problem you can use my xml/db scheme - it allows for custom elements, then you just need to modify the form with javascript.

Posted: Fri Feb 02, 2007 10:09 am
by pickle
You do realize this basically gives the user a loaded gun that is too heavy to point anywhere than their foot.

To implement that, a simple custom form that displays all the fields in the table should work fine. Just provide all the CRUD functionality to the form & viola!.

Re: Dynamic schema tables

Posted: Fri Feb 02, 2007 1:04 pm
by alex.barylski
arborint wrote:
Hockey wrote:Say you want to allow an end user the ability to add/remove fields from a table, how would you go about implementing such a thing?
You could store them in a second table containing a key relating back to the main record, the field name and the field value.
Hockey wrote:Here's the thing...the client using a simple interface says "OK I want to add a Middle Name field to my contacts DB".
Maybe create an interface that allows the client to ALTER the database schema. It shouldn't be too difficult to add and remove fields. Then your Active Record implementation could use DESCRIBE to get the actual schema.
The DESCRIBE method might work...didn't think of that...

I would prefer it was all in a single table, so queries were easy to construct and understand/debug, etc...

Thanks :)

Re: Dynamic schema tables

Posted: Fri Feb 02, 2007 5:03 pm
by Christopher
Hockey wrote:The DESCRIBE method might work...didn't think of that...
Yeah, but DESCRIBE is slow so if you go that way you probably want to cache the schema. You could also rewind completely and create a screen that asked the client what they want during installation and then create the table and a matching ActiveRecord class up front.

Posted: Fri Feb 02, 2007 7:21 pm
by Jenk
Just give them phpMyAdmin :p

Posted: Fri Feb 02, 2007 9:16 pm
by Ambush Commander
Dynamically changing schemas is not something that should be taken lightly, and if you need to do it outside of updating or maintenance, there's something seriously wrong, IMO. There's other ways of implementing similar functionality.

Posted: Fri Feb 02, 2007 10:43 pm
by DaveTheAve
Serialize the data in store it in a 'extras' field problem solved. That way there is less overhead with multiple tables linking everywhere or extra load for deleting and removing rows. Best of all, if there is STILL too much overhead from serializing the 'extras' data gzcompress() it!

Posted: Fri Feb 02, 2007 11:01 pm
by feyd
..And make searching extra fun. :)

Posted: Fri Feb 02, 2007 11:46 pm
by Christopher
Ambush Commander wrote:Dynamically changing schemas is not something that should be taken lightly, and if you need to do it outside of updating or maintenance, there's something seriously wrong, IMO. There's other ways of implementing similar functionality.
Actually I don't think databases should be any more sacrosanct that anything else we regularly treat dynamically. We gleefully change innerHTML, we plug-in objects, rename and move files, etc. etc.

I would go with the scaffolding approach I later suggested, but there is really no reason why you could not, very easily, build a system that added and removed fields in table -- and then added those fields to forms and searches. It's just another SQL statement. And given that it would be a closed system I am sure it would be as robust as any other.

Posted: Sat Feb 03, 2007 9:56 am
by DaveTheAve
feyd wrote:..And make searching extra fun. :)
Never said he had to search, plus if he did, don't compress it. At least you got SOME type of search then. (Tho not the best)

Posted: Sat Feb 03, 2007 10:07 am
by Ambush Commander
Actually I don't think databases should be any more sacrosanct that anything else we regularly treat dynamically. We gleefully change innerHTML, we plug-in objects, rename and move files, etc. etc.
The trouble with shuffling around the database schema too much is that it affects everything else that already is in the database. Not too bad if you're just adding a column, but removing and changing columns becomes quite unwieldy when dealing with large datasets.