Dynamic schema tables
Moderator: General Moderators
-
alex.barylski
- DevNet Evangelist
- Posts: 6267
- Joined: Tue Dec 21, 2004 5:00 pm
- Location: Winnipeg
Dynamic schema tables
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???
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???
-
alex.barylski
- DevNet Evangelist
- Posts: 6267
- Joined: Tue Dec 21, 2004 5:00 pm
- Location: Winnipeg
Here's the thing...the client using a simple interface says "OK I want to add a Middle Name field to my contacts DB".wei wrote:may be something like Active Record + Scaffold (to borrow from ruby on rails terminology).
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...?
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: Dynamic schema tables
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: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?
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.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".
(#10850)
- Kieran Huggins
- DevNet Master
- Posts: 3635
- Joined: Wed Dec 06, 2006 4:14 pm
- Location: Toronto, Canada
- Contact:
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.
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!.
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
The DESCRIBE method might work...didn't think of that...arborint wrote: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: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?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.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".
I would prefer it was all in a single table, so queries were easy to construct and understand/debug, etc...
Thanks
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: Dynamic schema tables
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.Hockey wrote:The DESCRIBE method might work...didn't think of that...
(#10850)
- Ambush Commander
- DevNet Master
- Posts: 3698
- Joined: Mon Oct 25, 2004 9:29 pm
- Location: New Jersey, US
- DaveTheAve
- Forum Contributor
- Posts: 385
- Joined: Tue Oct 03, 2006 2:25 pm
- Location: 127.0.0.1
- Contact:
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
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.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.
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)
- DaveTheAve
- Forum Contributor
- Posts: 385
- Joined: Tue Oct 03, 2006 2:25 pm
- Location: 127.0.0.1
- Contact:
- Ambush Commander
- DevNet Master
- Posts: 3698
- Joined: Mon Oct 25, 2004 9:29 pm
- Location: New Jersey, US
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.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.