Dynamic schema tables

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Dynamic schema tables

Post 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???
wei
Forum Contributor
Posts: 140
Joined: Wed Jul 12, 2006 12:18 am

Post by wei »

may be something like Active Record + Scaffold (to borrow from ruby on rails terminology).
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Post 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...?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Dynamic schema tables

Post 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.
(#10850)
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post 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!.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Dynamic schema tables

Post 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 :)
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Dynamic schema tables

Post 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.
(#10850)
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

Just give them phpMyAdmin :p
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post 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.
User avatar
DaveTheAve
Forum Contributor
Posts: 385
Joined: Tue Oct 03, 2006 2:25 pm
Location: 127.0.0.1
Contact:

Post 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!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

..And make searching extra fun. :)
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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.
(#10850)
User avatar
DaveTheAve
Forum Contributor
Posts: 385
Joined: Tue Oct 03, 2006 2:25 pm
Location: 127.0.0.1
Contact:

Post 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)
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post 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.
Post Reply