Introduction to Basic Database Concepts in Healthcare
This training session provided by the Health Science Center IT focuses on fundamental database design principles, specifically for healthcare applications. Participants learned about tables, fields, and records, including how to structure relational databases effectively. Key topics include unique field names, primary keys, and the significance of having unique records. Emphasis is placed on data integrity, avoiding calculated fields in tables, and understanding the logical breakdown of data into its smallest parts for optimal querying and reporting.
Introduction to Basic Database Concepts in Healthcare
E N D
Presentation Transcript
Access 2007: Multiple Tables Health Science Center IT Center – Training training@health.ufl.edu 352-273-5051
Name: Barry HarrisCollege: MedicineTel: 392-5555 Basic Database Concepts • Table • A set of related records • Record Name: Barry HarrisCollege: MedicineTel: 392-5555 • A collection of data about an individual item • Field Name: Barry Harris • A single item of data common to all records
An Example of a Table Fields Records
Review of the Basic Design Rules of Relational Databases • Unique Field Names • Keep fields unique across tables, and keep them as clear as possible in each table. • No Calculated or Derived Fields • Calculations and derivations can be performed in Queries, Forms and Reports. Doing them in a table only increases the chance of data entry error.
Review of the Basic Design Rules of Relational Databases • Data is broken down into Smallest Logical Parts • Smallest “Sortable” parts. Remember it’s mucheasier to pull fields together than it is to pull a field apart. • Unique Records • Each of your tables should have unique records. We ensure this by setting one field to be a Primary Key. This can be a user generated field or an AutoNumber.
Basic Design Rules We use the unique primary key as our link between our tables, this helps ensure we connect to the correct record. • Unique Records
Planning our Patient Table Patient Table (from previous workshop) Pt Med Rec # Pt First Name Pt Last Name Pt Gender Pt Birth Date Pt Phone
Planning our Appointment Table Who? What? When? Where? Why? Patient/Doctor Type of visit Date/Time Location Reason What have I got? Patient Name Patient DOB Patient Phone Doctor Name Visit Type Date/Time Location Reason
Planning our Appointment Table Does the data belong in this table? Patient Name Patient DOB Patient Phone Doctor Name Visit Type Date/Time Location Reason Pt Med Rec # Pt First Name Pt Last Name Pt Gender Pt Birth Date Pt Phone Pt Med Rec #
Planning our Appointment Table Smallest Logical Parts • Pt Med Rec # • Doctor Name • Visit Type • First, Referral, Physical • Date • Time • Location • Shands, MagCtr, Med Plaza • Reason
Planning our Appointment Table Unique Field Names • Pt Med Rec # • Appt Doctor • Appt Type • First, Referral, Physical • Appt Date • Appt Time • Appt Location • Shands, MagCtr, Med Plaza • Appt Reason
Planning our Appointment Table No Calculated or Derived Fields • Pt Med Rec # • Appt Doctor • Appt Type • First, Referral, Physical • Appt Date • Appt Time • Appt Location • Shands, MagCtr, Med Plaza • Appt Reason
Planning our Appointment Table Primary Key • (AutoNumber) • Pt Med Rec # • Appt Doctor • Appt Type • First, Referral, Physical • Appt Date • Appt Time • Appt Location • Shands, MagCtr, Med Plaza • Appt Reason
Planning our Appointment Table Multiple Answer Questions MANY OPTIONS Appt Type ONLY ONE Appt Location
Planning our Appointment Table Final Plan Appt ID # Pt Med Rec # Appt Doctor Appt Date Appt Time Appt Location Appt Reason Appt Type First Appt Type Referral Appt Type Physical • AutoNumber • Text(from Patients table) • Text • Date/Time • Date/Time • Text (Shands, MagCtr, Med Plaza) • Memo • Yes/No • Yes/No • Yes/No
