Flask Database Model Design

Question

I am developing my first website in Flask using PostgreSQL and I want my database to have professors from all universities and courses in the USA.

As I am not very familiar with databases I have experienced some issues with my database model design.

Basically, I want to have a Professor, College and Course models.

1) A professor has:

• An id (primary key)
• A name
• A college (foreign key)
• A course (foreign key)

2) A college has:

• An id (primary key)
• A name (e.g. Harvard University)
• Many professors

3) A course in my model is not a college-specific course (e.g. Harvard CS) but a subject which can be identical throughout many colleges, e.g. Biology. It therefore has:

• An id (primary key)
• A name (e.g. Biology)
• Many professors

My end goal is just to have a list of all professors and people can search through them by filtering according to a subject, college, or both.

As you can see I haven't implemented a relationship between colleges and courses.

This is my code:

class Professor(db.Model):
  id = db.Column(db.Integer, primary_key=True)
  name = db.Column(db.String(80), unique=False)
  college_id = db.Column(db.Integer, db.ForeignKey('college.id'))
  course_id = db.Column(db.Integer, db.ForeignKey('course.id'))

class College(db.Model):
  id = db.Column(db.Integer, primary_key=True)
  name = db.Column(db.String(120), unique=True)
  professors = db.relationship('Professor', backref='college', lazy='dynamic')

class Course(db.Model):
  id = db.Column(db.Integer, primary_key=True)
  name = db.Column(db.String(120), unique=True)
  professors = db.relationship('Professor', backref='course', lazy='dynamic')

I was wondering if this is a good database design. Am I going to be able to do what I'm aiming for with the DB models staying the same?

Answer 1

I think at least the relationship between Professor and Course is many to many.

courses_professors = db.Table(
    db.Column(db.Integer(), db.ForeignKey("course.id"),
    db.Column(db.Integer(), db.ForeignKey("professor.id"))

class Course(db.Model):
    id = db.Column(db.Integer(), primary_key=1)
    name = db.Column(db.String(128), unique=1)

class Professor(db.Model):
    id = db.Column(db.Integer(), primary_key=1)
    name = db.Column(db.String(64))
    courses = db.relationship("Course", secondary=courses_professor, backref=db.backref("professors")

If the relationship between Professor and College is also many to many, you can also define a association table between them as preceding codes.