Re: [suse-linux-uk-schools] MIS, databases
I'd be interested to see the category of problem that would be difficult without stored procedures (not that I'm an expert on them, but I've never yet been in a situation where I haven't been able to do what I need for lack of them.
If the project was conducted in public, authors of the database structure would have no control over what clients others may or may not write.
I think that if the project were to be well designed, I would hope that there would be no need whatever for anyone to even consider writing a client that provided any functionality beyond that deliverable in a browser. If someone felt the need to do that, then the design is wrong. Cheers -- Phil Driscoll Dial Solutions +44 (0)113 294 5112 http://www.dialsolutions.com http://www.dtonline.org
I'd be interested to see the category of problem that would be difficult without stored procedures (not that I'm an expert on them, but I've never yet been in a situation where I haven't been able to do what I need for lack of them.
Hello Phil, I do not claim to be an SQL expert either, there are situations, I'm sure where I've written procedural code where an SQL set-based expression would have done the job. However, consider this: Pupils may be entitled to free school meals (fsm). A local authority will provide the school with a list of pupils who are thus entitled from say start_date to end_date. The system keeps a record of periods of entitlement for each pupil so that should some query be requested, involving say total_number_of_days_of_fsm_entitlement and GCSE_outcomes then it could be done. The system produces printed output each week, namely a grid covering a (say) two week period, with days across the top and pupil names down the side. In each cell is indicated whether the pupil is entitled to fsm or not. This printout is used at the entrance to the dining area. When a pupils period of fsm entitlement expires, the school allows a (configurable, looked up from a table of user defined settings) number of days grace before refusing to provide a meal. The printout indicates such grace days in the appropriate cell on the sheet. However, the system also has tables recording periods of school holiday, and these do not count against grace days. So, the report has to generate the grid and fill in each cell with: Entitled, Not entitled, Holiday or Grace. I wanted to pass a result set to the reporting module, and found myself unable to do it without resort to a procedure. I ended up creating temporary tables, looping through the pupils doing INSERTS to them, SELECTing from these temp tables JOINed and then DROPping the temporary tables. Another recent tricky one, connected with the Attendance Monitor component I wrote (screenshot at my site www.openi.org) was where I wanted to process temporary tables, whose size was not known in advance. I ended up using two nested cursors. Ugh! Now I know some of this may be down to my inability to think in set terms as opposed to procedures, but probably not entirely so. I have of course tried hard to model all this stuff in a proper normalized way. regards Richard richard@tortoise.demon.co.uk
On Sat, 5 May 2001 richard@tortoise.demon.co.uk wrote:
I'd be interested to see the category of problem that would be difficult without stored procedures (not that I'm an expert on them, but I've never yet been in a situation where I haven't been able to do what I need for lack of them. I do not claim to be an SQL expert either, there are situations, I'm sure where I've written procedural code where an SQL set-based expression would have done the job. However, consider this: Pupils may be entitled to free school meals (fsm). A local authority will provide the school with a list of pupils who are thus entitled from say start_date to end_date. The system keeps a record of periods of entitlement for each pupil so that should some query be requested, involving say total_number_of_days_of_fsm_entitlement and GCSE_outcomes then it could be done. The system produces printed output each week, namely a grid covering a (say) two week period, with days across the top and pupil names down the side. In each cell is indicated whether the pupil is entitled to fsm or not. This printout is used at the entrance to the dining area. When a pupils period of fsm entitlement expires, the school allows a (configurable, looked up from a table of user defined settings) number of days grace before refusing to provide a meal. The printout indicates such grace days in the appropriate cell on the sheet. However, the system also has tables recording periods of school holiday, and these do not count against grace days. So, the report has to generate the grid and fill in each cell with: Entitled, Not entitled, Holiday or Grace.
As a (fairly rough) first approximation, how about: Create a function, called e.g. fsm_status, that takes as inputs the pupil ID and the date, and returns the appropriate status ("Entitled", etc.). Exactly how you do this depends on how the data about school meal periods, holidays etc. is stored, and also depends on whether the start_date and end_date in the list from the local authority are common to all pupils or not (I presume that they are). Create a query that looks something like: SELECT pupils.name, fsm_status(pupils.id, today), fsm_status(pupils.id, today+1), ... (optional) Create a view, called e.g. fsm_report, based on this query. Involves a stored function, but not temporary tables, and the end result is that you can just SELECT * FROM fsm_report and feed it straight to the report generator. Michael
participants (3)
-
Michael Brown -
Phil Driscoll -
richard＠tortoise.demon.co.uk