Dynamic form generation using a normalized database - PowerPoint PPT Presentation

1 / 18
About This Presentation
Title:

Dynamic form generation using a normalized database

Description:

Annual survey to find out about fees being assessed by local governments ... Survey page loops through array to display form questions (and answers, if any exist) ... – PowerPoint PPT presentation

Number of Views:24
Avg rating:3.0/5.0
Slides: 19
Provided by: aber2
Category:

less

Transcript and Presenter's Notes

Title: Dynamic form generation using a normalized database


1
Dynamic form generation using a normalized
database
  • by Ann Bernstein
  • Twin Cities CFUG
  • September 1, 2004

2
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

3
Project Requirements
  • secure logins for 3,000 potential users (cities,
    counties, and townships)
  • users can save data between logins
  • easy to usesome users are inexperienced
  • admin features

4
Heres what the survey form looks like
5
(No Transcript)
6
(No Transcript)
7
Tour the web app online...
8
Two key design considerations
  • nearly all data is quantitative
  • content of survey was not finalized when we began
    development.

9
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.

10
Heres a flat-file database
Q1_03 Q2_03 Q3_03 Q4_03 ...Q54_03
Aitkin County 50 250
Anoka County 13,000 10
Becker County 250
11
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.

12
Heres a normalized database
UserID Year QuestionID Amount
2 2003 Q1 250
2 2003 Q7 45
17 2003 Q1 2,000
150 2003 Q23 30
150 2003 Q54 1,000
13
Normalized db advantages
  • Easy to query
  • Flexible questions could be modified without
    changing structure.
  • Dense data storage
  • Allows for dynamic CF form!

14
Our final database structure
15
Main CF pages used in survey
  1. Initialization page creates 2d array in session
    variable to store questions and answers.
  2. Survey page loops through array to display form
    questions (and answers, if any exist).
  3. Validation page plugs form data into array,
    checks data, and submits it to database if valid.
    If not, form redisplays with error highlighted.

16
Advantages of dynamic form with normalized db
  • Code is short and sweet. Debugging is much faster
    because youre 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.

17
Room for improvement (any suggestions?)
  • 2d array is awkward to use.
  • Hard to read code (what does aryQA17 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.

18
Questions/comments?
  • Ann Bernstein
  • (651) 215-0258
  • ann.bernstein
  • _at_moea.state.mn.us
Write a Comment
User Comments (0)
About PowerShow.com