Database design in the real world

1. Database design in the real world 10 practical lessons learned from the field Stephanie M Randolph School of Health, Physical Education, and Recreation October 17, 2007

2. About Me • Stephanie M. Randolph • Web and Database Manager • School of Health, Physical Education, and Recreation • MIS 2006 • National Center on Accessibility • UITS – Data Management Support • Division of Recreational Sports

3. Lesson #1 Know the fundamentals of proper database design • Normalization, entities, attributes, domains, relationships, keys • Understanding now will save time and frustration in the future • Example: ER Diagram

4. 1-M Individual Phone Education College/University Code College/University City College/University Name College/University State College/University Country Begin Date End Date Degree Code Degree Name Hours Completed Date Degree Anticipated/Received US GPA Non-US Grade Average Major Minor Reason for Attending 1-M First Name Middle Name Last Name Suffix Other First Name Other Middle Name Other Last Name Other Suffix Email Address Gender Ethnic Information Date of Birth City of Birth State of Birth Country of Birth Country of Citizenship Visa Status Native Language Native Language list Parent/Guardian First Name Parent/Guardian Middle Initial Parent/Guardian Last Name Parent/Guardian Relationship Parent/Guardian Address 1 Parent/Guardian Address 2 Parent/Guardian Address 3 Parent/Guardian Address 4 Parent/Guardian City Parent/Guardian County Parent/Guardian State Parent/Guardian Zip Parent/Guardian Country Parent/Guardian Home Phone Academic Program Major Academic Interest Semester of Enrollment Other Universities (multi?) State Prior to Attending Indiana College Require Housing (Y/N) Affirmation (Y/N) Student Comments Personal Statement Resident? (Y/N) Current Application (Y/N) Which Fellowship CIC Participant (Y/N) Project 1000 Participant (Y/N) McNair Scholar (Y/N) MEDIC-B Scholar (Y/N) National Physical ScienceConsortium (Y/N) Academic Honors Extracurricular Activities Native Languages Languages Translated Languages Speak Read Write Thesis (Y/N) IU Assistantship (Y/N) Dates Reachable Current Dates Reachable Permanent FAFSA Forms Computer Skills Primary Area of Interest Health Science Content School Health Education Public Health Education Driver Education Driver Education Advanced Phone Number Home, Work, Fax, Pager, or Mobile? Work Experience Job Title Employer Name City State Phone Start Date End Date 1-M Recommendations 1-M First Name Last Name Street Address 1 Street Address 2 City State Zip Code Country Phone Email Address Title Employer Relationship Waive Right (Y/N) Submit Online (Y/N) Personal Note Other Experience Role Played Event Dates 1-M Test Scores GRE Test Date GRE Verbal Score GRE Verbal Percentile GRE Quantitative Score GRE Quantitative Percentile Analytical (< 10/1/02) Percentile (< 10/1/02) Analytical Writing (> 10/1/02) Analytical Writing Percentile (> 10/1/02) Subject (> 10/1/02) Subject Percentile (> 10/1/02) Results Sent to IU (Y/N) Which Campus TOEFL Test Date TOEFL Listening Score TOEFL Structure and Writing Score TOEFL Reading Score TOEFL Essay Score TOEFL Total Score Computer or Test Based Results Sent to IU (Y/N) Which Campus 1-M Publications Type Title Journal List of Authors Page Numbers Dates 1-M ARC Certifications Type Home office Expiration date Public Health Education Driver Education Driver Education Advanced Other Certifications Professional Writing Experience Review Literature Design Study Collect Data Analyze Data Writing Up Results Teaching Experience Biomechanics Exercise Physiology Measurement/Statistics Motor Learning/Motor Control Sport Psychology Adapted Physical Education School Camp Disability Type Other NATA Certified (Y/N) NATA Certification Number Why Not Certified AT Degree Track EMT Paramedic Physical Therapy Other AT Sports Position Preference 1 Position Preference 2 Position Preference 3 1-M Activities Name Type Teaching Experience 1-M Address Address 1 Address 2 Address 3 Address 4 City County State Zip Country Current, Permanent or New? Length of Current Permanent Date of Address Change 1-M Residency Beginning Date Address City County Zip 1-M Teaching Experience Grade Course Years Level 1-M

5. Lesson #2 Plan things right from the beginning • Don’t rush this phase • The more thought given at the start will equate to less work down the road • Users and/or administration will sometimes have unrealistic expectations • Example: PhD Admissions Database

6. Lesson #3 Think beyond the *what* • In terms of data you want to know *Who, where, when, and how* • Data is worthless unless it can be applied • Examples: HPER Technology Requests and NCA Site Evaluations

8. Lesson #4 Know your users • Beyond interviews – watch them • Know what they do and what causes them problems and what is easy for them • Learn their lingo • Get their feedback • The better your relationship, the better the system • Example: PhD Admissions Database

9. Lesson #5 Know how *things* work in your organization • Get to know administration • Get to know future plans or, better yet, BE involved with future planning • Next to your manager, future changes will affect you the most • You need to have a stake in the future • Think *details* AND *big picture*

10. Lesson #6 Develop your system based on current procedures; DON’T create new procedures based on a new system! • Not basing on current procedures leads to resistance from users and/or just makes it harder for them to adapt • Modeling • Example: PhD Admissions Flow Chart

12. Lesson #7 Develop flexible, extensible systems • Things WILL change OR you won’t anticipate a future need • Example of what not to do: Staff Bios and phpMyAdmin

13. Lesson #8 Be aware of surrounding systems • Don’t design in a vacuum and don’t reinvent the wheel • Other systems already in place may be used instead of or in conjunction with your system • Saves time and can be helpful to your users • Example: IUIE and ApplyYourself

14. Lesson #9 Be prepared to fix the work of others • Humor • http://www.thecrumb.com/2007/06/20/bad-database-design-why/ • http://secretgeek.net/badtables.asp

15. Lesson #10 Don’t go for the “WOW!”; go for the “Ahhh!” • You want the transition from pre-launch to post-launch to be seamless • You want your users to feel comfortable and at ease, not in shock • The more technically complex your system is the greater the chance of failure • Start small and build over time • Example: SIP