3
\$\begingroup\$

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.