Looking for input in model design for Django Schools

Question

Today I'm starting a little project to create a Django based school administration program. I'm currently designing the models and their corresponding relationships. Being rather new to Django and relational databases in general, I would like some input.

Before I show you the current model layout, you need to have an idea of what the program is meant to do. Keep in mind that it is my goal for the software to be usable by both individual schools and entire school systems.

Features: - Create multiple schools
- Track student population per school
- Track student demographics, parent contact info, etc.
- Grade books
- Transcripts
- Track disciplinary record.
- Fees schedules and payment tracking
- Generate reports (student activity, student transcripts, class progress, progress by demographic, payment reports, disciplinary report by student class and demographic)
-- Automated PDF report email to parents for student reports.

Given those feature requirements, here is the model layout that I currently have: Models

* Person
      o ID: char or int
      o FirstName: char
      o MiddleName: char
      o FamilyName: char
      o Sex: multiple choice
      o Ethnicity: multiple choice
      o BirthDate: date
      o Email: char
      o HomePhone: char
      o WordPhone: char
      o CellPhone: char
      o Address: one-to-one with Location
* Student (inherent Person)
      o Classes: one-to-many with Class
      o Parents: one-to-many with Parent
      o Account: one-to-one with PaymentSchedule
      o Tasks: one-to-many with Tasks
      o Diciplin: one-to-many with Discipline
* Parent (inherent Person)
      o Children: one-to-many with Student
* Teacher (inherent Person)
      o Classes: one-to-many with Class
* Location
      o Address: char
      o Address2: char
      o Address3: char
      o City: char
      o StateProvince: char
      o PostalCode: char
      o Country: multiple choice
* Course
      o Name: char
      o Description: text field
      o Grade: int
* Class
      o School: one-to-one with School
      o Course: one-to-one with Course
      o Teacher: one-to-one with Teacher
      o Students: one-to-many with Student
* School
      o ID: char or int
      o Name: char
      o Location: one-to-one with location
* Tasks
      o ID: auto increment
      o Type: multiple choice (assignment, test, etc.)
      o DateAssigned: date
      o DateCompleted: date
      o Score: real
      o Weight: real
      o Class: one-to-one with class
      o Student: one-to-one with Student
* Discipline
      o ID: auto-increment
      o Discription: text-field
      o Reaction: text-field
      o Students: one-to-many with Student
* PaymentSchedule
      o ID: auto-increment
      o YearlyCost: real
      o PaymentSchedule: multiple choice
      o ScholarshipType: multiple choice, None if N/A
      o ScholarshipAmount: real, 0 if N/A
      o Transactions: one-to-many with Payments
* Payments
      o auto-increment
      o Amount: real
      o Date: date

If you have ideas on how this could be improved upon, I'd love to year them!

Update

I've written the initial models.py code, which is probably in need of much love. If you would like to take a look, or even join the project, check out the link.
http://bazaar.launchpad.net/~djangoschools/djangoschools/trunk/files

Answer 1

From a quick look I think that its fairly comprehensive. Perhaps you should allow multiple teachers on a course, and allow reuse of addresses/locations between parents and students.

As a general rule I would say that you should start implementing and then you will find where you need improvement.

Answer 2

Looks like an interesting project. Do note that Django has higher-level types than SQL, so you can make use of things like the email address type.

If you're planning on targeting GAE, you should find a similarly rich set of model types.

Answer 3

hey...i agree...it looks pretty good. someone advised me to use auto-increment on all tables just to be sure there really is a unique id on every record. it's your choice if you'd like to go that route.

Answer 4

You should link paiement (transaction) to the person concerned.

Answer 5

Some possible issues:

For the location object, what if in the future you need to hold a home address, work address, etc. for a person? Same for email addresses and phone numbers - I would have phone numbers be their own object.

Include an Address_3 on your address object.

Answer 6

I would advise you to not worry about the underling relational database. Yes, you'll need to understand what a foreign key is and the difference between many-to-many and one-to-many, etc., but you should think about your models in terms of Django classes. That's how you'll have to write them anyway, so that's where I would start. The Django documenation on models is great, and will help you a lot.

I think everyone here would be glad to help you with the Python classes; you should rewrite your example using Django. For example, your Person table would look like this:

from django.db import models

SEX_CHOICES = (
    ('M', 'Male'),
    ('F', 'Female')
)

ETHNICITY_CHOICES = (
    # follow the same format as SEX_CHOICES:
    # (database_value, human_friendly_name)
)

class Person(models.Model):
    first_name   = models.CharField(max_length=200)
    middle_name  = models.CharField(max_length=200)
    familiy_name = models.CharField(max_length=200) 

    sex = models.CharField(max_length=1, choices=SEX_CHOICES)
    ethnicity  = models.CharField(max_length=1, choices=ETHNICITY_CHOICES)
    birth_date = models.DateField()

    email = models.EmailField()
    home_phone = models.CharField(max_length=10) # USA phone numbers
    work_phone = models.CharField(max_length=10)
    cell_phone = models.CharField(max_length=10)
    address = models.ForeignKey(Location)

class Location(models.Model):
    # left as an exercise for the reader

# more classes...

Answer 7

A student doesn't have a class. He/She attends a class that has them (in the roster). Here's another way to look at the class situation. (notice the model's name. that's just because I tend not to name anything 'Class' because it's easy to get into name clashes that way.)

class SchoolClass(models.Model):
    teacher = models.ManyToManyField(Teacher, related_name='teachers')
    student = models.ManyToManyField(Student, related_name='students')
    prerequisites = models.ForeignKey('self')
    startdate = models.DateField()
    enddate = models.DateField()
    ... and so on ...

This is more natural because you can have a class with students and take attendance according to the students list, or aggregate grades, student ages, etc. in a natural way.