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?