trjhtr

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',
 cascade='all, delete-orphan',
 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",
 cascade="all, delete-orphan",
 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