Dynamic form generation using a normalized database by Ann Bernstein Twin Cities CFUG September 1, 2004
MSW Fees Survey: an “e-government” project • Annual survey to find out about fees being assessed by local governments (counties, cities, towns) for solid waste management services. • Our first year as the data collection agency; we want to make a good impression! • Last year we collected other data online and it was very successful
Project Requirements • secure logins for 3,000 potential users (cities, counties, and townships) • users can save data between logins • easy to use—some users are inexperienced • admin features
Two key design considerations: • nearly all data is quantitative • content of survey was not finalized when we began development.
Flat file or normalized database? • We used a flat-file last time. Code was lengthy and repetitive, and therefore prone to bugs. • This time we decided to try something new and use a normalized database.
Flat file issues: • Difficult to query data in separate columns. • Structure must be modified every time survey questions are added/deleted/modified. • Lots of empty cells in database.
Normalized db advantages: • Easy to query • Flexible— questions could be modified without changing structure. • Dense data storage • Allows for dynamic CF form!
Main CF pages used in survey: • Initialization page creates 2d array in session variable to store questions and answers. • Survey page loops through array to display form questions (and answers, if any exist). • Validation page plugs form data into array, checks data, and submits it to database if valid. If not, form redisplays with error highlighted.
Advantages of dynamic form with normalized db • Code is short and sweet. Debugging is much faster because you’re only creating a single form element! • Questions can be added or changed until the last minute. • Data analysis is fast and easy • Application is reusable.
Room for improvement (any suggestions?) • 2d array is awkward to use. • Hard to read code (what does aryQA refer to?) • Index must correspond to key field, so keys must be kept in perfect sequence. • Admin features were afterthought & klugey • Good database design does not necessarily result in easy coding in CF.
Questions/comments? Ann Bernstein (651) 215-0258 ann.bernstein @moea.state.mn.us