4
\$\begingroup\$

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?

\$\endgroup\$

1 Answer 1

1
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.