I'm seeking a review of my Flask-SQLAlchemy model structure, particularly adherence to DB best practices, such as avoiding duplication, etc.

The models represent the relations between Departments, Teams, and Roles. A more detailed explanation of these has been included.

Model Specification:

• Department: A Department represents a collection of Teams. A Department without Teams should be considered valid. On deletion, all Teams associated with the Department should also be deleted.

• Team: A Team represents a collection of Roles. A Team without Roles should be considered valid. On deletion, all Roles associated with the Team should also be deleted. A Team may only belong to one department.

• Role: A Role may only belong to one Team.

Models:

class Department(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(64), nullable=False)

teams = db.relationship(
'Team',
backref='department',
lazy='dynamic'
)

def __repr__(self):
return '<Department {} >'.format(self.name)

class Team(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(64), nullable=False)

roles = db.relationship(
'Role',
backref="team",
lazy='dynamic'
)

department_id = db.Column(
db.Integer,
db.ForeignKey('department.id'),
nullable=False
)

def __repr__(self):
return '<Team {} >'.format(self.name)

class Role(db.Model):
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(64), nullable=False)

team_id = db.Column(db.Integer, db.ForeignKey('team.id'), nullable=False)

def __repr__(self):
return '<Role {} : ID {} >'.format(self.name, self.id)


Questions:

Had I written this as SQL, I would have expected Role to contain both the Department_id and Team_id. Can the same structure be achieved using SQL-Alchemy? Is the current (sub-optimal?) structure symptomatic of an ORM?

Update

The tables this produces are as follows:

Department: ID, Name
Team: ID, Name, department_id
Role: ID, Name, role_id

• Could you share your DB scheme? – Mast Apr 7 '18 at 14:56
• @Mast, I've added an update to show the tables produced. – DavidSaucony Apr 7 '18 at 15:04
• You needn't store department_id in Role. You can get department_id of a Role with role.team.department_id. – stamaimer May 10 '18 at 3:20