Can it be done? Webform GUI that enables user-defined database fields and queries

Imagine a module that had the flexibility of webform module, with the ability to access and modify information in a database of your choosing. Schools are notoriously sold up the river by proprietary student information systems that are often mutually-incompatible and difficult to use, particularly in a manner that enables teachers, students, and parents access from home or regular old internet-connected PCs at school. If one were to migrate existing school databases into CSV format and import them into MYSQL, Drupal could manage content on a user-by-user basis that was relevant to a student's schedule, the teacher's classes, advisory caseloads, etc. When I assign homework in my classes, my students would see it in their homework lists, when anecdotal reports are due for my classes, I could submit them online and have them stored along with other student data...etc. Similar applications could be imagined for client relations.

The difficulty with building this kind of module is that school data systems are so diverse that on first glance, a custom module would require an extended, complex development time for any given school. The solution, I think, would be a webform-like module that accepts database access and query information from the form author by way of a GUI that allows each form field to be attached to database fields and rows of the author's choosing. This would obviously be an administrative module, for users with enough mySQL database access to be able to map fields into their forms, but it would drastically speed the development time for customized Drupal student/customer information tracking applications.

I am still a novice PHP programmer, and I don't have enough knowledge of the Drupal core to build this myself, but I imagine it would be a major asset to Drupal. Any interest out there?

Have you looked at flexinode and taxonomy access modules?

paddy_deburca - April 13, 2006 - 12:00

The flexinode module allows you to define the elements you need in a node and taxonomy access allows you to grant detailed access to individual nodes.

Instead of creating on big module - or flexinode types - to cover all aspects of schooling; you could break it down to multiple flexinode types each dealing with a specific area.

If the universe of students is known, each student could be a taxonomy leaf, and by using the taxonomy access module, specific accesses could be granted to specific users - a student would only be able to see his/her exam results; whereas, all students would be able to see all class timetables - but normal visitors would not.

I describe a complicated solution to the requirements you have. Neither flexinode nor taxonomy access are simple modules to master - but they may give you enough information to customise or develop your own solution.

Paddy.

http://deburca.org, and http://amadain.net

Can this be done efficiently?

Andrupal - April 13, 2006 - 19:43

I appreciate your suggestion, but the solution you describe sounds nightmarish given the number of students and frequent schedule changes that occur in a typical high school. I can't imagine tweaking user access privileges to develop user-specific content accessible only to a role assigned for each course we offer. This becomes especially unlikely given that we already pay someone to sit and tweak student schedules in a separate, proprietary database interface. All of this student information already exists in CSV tables, albeit in a structure that might not easily integrate with Drupal's user data. Recreating this information each semester in taxonomy and access rules simply wouldn't fly in an institution that can barely find the courage to use email. We need relative simplicity and sustainability.

Unless, are you suggesting that I use some mechanism that mass-imports student and teacher schedules as a set of taxonomies and roles? How might this be done?

Your assumption about the

bonobo - April 13, 2006 - 20:19

Your assumption about the scalability of TAC and flexinode to solve this is right on -- this would not be sustainable in an academic setting.

Really, this is something that would be best handled by a set of complementary modules -- a custom profile module, a class description module, and a schedule module -- the schedule module would hold the info about meeting times (course rotation) -- possibly, scheduling would be split into two modules: one to manage the period rotation (ie, per 1 meets Mon, Tues, Th, Fri from 8-8:50) and a second to manage student schedules (Jimmy has English per 1, Spanish per 2, etc)

Simply designing the database structure and the UI to manage that data would be the first step -- the second step would be to make some decisions about how "smart" you wanted the software to be -- for example, would users just input data, or should the app be able to spot conflicts (a student or teacher scheduled to be in two classes on the same period, as one example)

So, this is a manageable task, but the complexity of the task would be defined by the precise details you wanted to manage.

Cheers,

Bill

-------
http://www.funnymonkey.com
Tools for Teachers

...what about importing existing data?

Andrupal - April 14, 2006 - 03:38

Part of the issue for our school is that we are locked into the deathstar NYC dept of education, which in an effort to create economies of scale and accountability has centralized everything down to the toilet paper.

Schools everywhere face the task of integrating their %$^%$ proprietary data management packages in a way that end users (teachers and students) can actually benefit from. Building another, redundant system that requires lots of manual data-entry will not lead to adoption in academic settings, I would argue, even as you have sketched it out above. Most schools already have some kind of programming software...I don't think Drupal should try to overtake this domain...just integrate with it.

I'm actually looking for a module flexible enough to take ANY mySQL database and connect user-defined fields to forms, queries to tables, etc. While I recognize this approach would mean a steeper learning curve on the part of the school site administrator, it would also allow for greater sustainability, as long-term, low-maintenance data integration would be afforded with all the up-front investment of time and energy.

I imagine this could also have a much wider application than for schools, meaning a greater interest in a high quality development cycle. Think of this proposal as a flexinode/webform on mySQL steroids. Almost like a module-authoring module. Am I making sense?

Ruby on Rails

dwees - October 11, 2007 - 06:10

Ruby on rails is this flexible. Maybe Drupal is the wrong tool? I did a test development using Ruby on Rails and I create a sample table in the database, and very easily Ruby on Rails picked up its structure and created a Web form for me.

Dave

My site: http://www.unitorganizer.com/myblog

I had forgotten about this thread

bonobo - October 11, 2007 - 10:07

But, in the nearly 18 months since this was originally posted, the world of Drupal has changed dramatically --

What follows is the fruit of about 5 mins of thought:

If I was looking to solve this now, I'd do it (like many things now) via cck. Create the cck external db/external fields module. In the admin section, you could specify the external data source, and a username/pw with access to the source, and possibly even specific tables/fields to include or exclude. Of course, it'd be beneficial to have a key/index to help manage your data (ie, connect a user in one system with the same user on the other, so that a comment about Little Jimmy in system A is still about Little Jimmy in system B). But given that we're talking vaporware, why not?

Cheers,

Bill

-------
http://www.funnymonkey.com
Tools for Teachers

Proprietary Database?

dwees - October 11, 2007 - 11:17

I suspect the problem we will find here is that NYC DOE is likely using a proprietary database using flat files. I'm going to guess that no matter what we do, the 1980s architecture that is still in use in NYC DOE will stymie us. Maybe the best idea is to contact IBM, who apparently has the contract to manage the NYC Department of Education database services and ask them to use something Drupal friendly.

Dave

My site: http://www.unitorganizer.com/myblog

 
 

Drupal is a registered trademark of Dries Buytaert.