Maintain accurate data on mobile populations
In the process of designing and developing databases, I sometimes encounter problems that I can't solve by creating table structures alone. Tracking people across the space-time continuum, for example, demands a more complex solution. I confronted this challenge when a client asked me for advice about developing a statewide method of assessing student and school performance in grades K-12.
Establishing the Requirements
Let's briefly review the background for this case study. One of the project's business requirements was to track students as they moved among school districts within the state—an effort that hadn't been successful in the past. Tracking needed to be accurate because the state had instituted school and school-district report cards to which extra funding was tied. In addition, both state and federal governments relied on accurate student numbers to establish appropriate basic funding levels, and the federal government relied on those numbers to fund programs for special-needs children.
The state also had to meet a related business requirement: avoid counting students more than once. Double counting is a big problem—especially in districts that include mobile populations—because it renders statistics inaccurate. Double counting occurs because some parents are reluctant to divulge their family's previous location, students move in from out of state, and students leave the state before graduating from high school.
Initially, you might think that maintaining accurate numbers of students at each school would be an easy task—you'd just require that every student's Social Security number be on file, then you'd track students by those numbers. After all, the methods used today to issue Social Security numbers to children of school age are quite reliable, according to publications of the Social Security Administration. (For a sample publication, see http://www.ssa.gov/policy/congcomm/testimony_072299.html.) In the real world, however, even this simple solution doesn't work all the time. For example, parents can still misrepresent legal documents, and new state residents and immigrants can become confused and give the wrong information about their children. Because of obstacles such as these, you can't successfully track every student through every school, but you can take steps to mitigate the problem.
Developing the Model
Instead of relying solely on Social Security numbers, you need to develop a multifaceted approach to tracking students within a state. Assuming that a state has established a centralized database that every school can access for student-identity information, you can use the positive identification issued to each student (the Social Security number, in this case) to set up supplemental checks to confirm the student's identity. Code or user processes in the form of triggers and stored procedures or code at a client- or mid-tier level can enable these supplemental checks, and table architecture should support the checks.
Because the first step in tracking students—setting up a centralized database—is beyond the scope of this article, I assume that a database is already in place. The next step is to establish use of the positive identification for each student. Figure 1, page 48, is a PowerDesigner 9.0 conceptual data model that I used in developing the tracking process. In Figure 1, I created four entities. The CHILD entity represents a person of the age required to attend school in grades K-12. SCHOOL is a geopolitical entity, usually a building or collection of buildings, where children are educated in grades K-12. SCHOOL_DISTRICT is a political entity composed of a collection of schools. The district sets policy, administers the schools, and acts as an intermediary between the schools and the state Department of Education. REGISTERED_STUDENT represents a child enrolled in a specific school. This entity is used for the duration of the child's academic experience.
The relationships among these entities are as follows: A SCHOOL_DISTRICT might administer zero to many SCHOOLs, whereas a SCHOOL belongs to only one SCHOOL_DISTRICT. A SCHOOL might include zero to many REGISTERED_STUDENTs, whereas a REGISTERED_STUDENT attends only one SCHOOL at a time. A CHILD can become a REGISTERED_STUDENT zero or many times (depending on how many different schools the child has attended), whereas a REGISTERED_STUDENT refers to one CHILD.