Title: Dynamic form generation using a normalized database
1Dynamic form generation using a normalized
database
- by Ann Bernstein
- Twin Cities CFUG
- September 1, 2004
2MSW 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
3Project 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
4Heres what the survey form looks like
5(No Transcript)
6(No Transcript)
7Tour the web app online...
8Two key design considerations
- nearly all data is quantitative
- content of survey was not finalized when we began
development.
9Flat 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.
10Heres 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
11Flat 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.
12Heres 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
13Normalized db advantages
- Easy to query
- Flexible questions could be modified without
changing structure. - Dense data storage
- Allows for dynamic CF form!
14Our final database structure
15Main 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.
16Advantages 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.
17Room 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.
18Questions/comments?
- Ann Bernstein
- (651) 215-0258
- ann.bernstein
- _at_moea.state.mn.us