CODEWITHSUNDEEP
python-3.xflasksqlalchemyflask-sqlalchemyflask-marshmallow
Gau Rav
Gau Rav

Reputation: 355

Many to Many relationship Flask sqlalchemy and marshmallow

Suppose i have 2 tables , Table 1 consist of users info and Table 2 consist of Branch info. table 1 and table 2 is related to each other by many to many relationship. e.g 1 user can work in multiple branches and 1 branch can have multiple users. so here there's no parent child concept. i was wondering if i have to create another table with schema and relate it to user and branch table using foreign key or shall i create an association table.

I have done this :

class UserBranchMap(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    branch_id = db.Column(db.Integer,db.ForeignKey('branch.id'))
    branch = db.relationship("Branch", backref=db.backref("UBMbranch", lazy="dynamic"))
    user_id = db.Column(db.Integer,db.ForeignKey('user.id'))
    user = db.relationship("User", backref=db.backref("UBMuser", lazy="dynamic"))
    created_at = db.Column(db.VARCHAR(20), nullable = False)
    created_by = db.Column(db.VARCHAR(20), nullable = False)

class UserBranchMapSchema(ma.Schema):
    branch = fields.Nested(branch_schema)
    user = fields.Nested(user_schema)
    class Meta:
        fields = ('branch_id','user_id','created_at','created_by')

userbranchmap_schema = UserBranchMapSchema()
userbranchmaps_schema = UserBranchMapSchema(many = True)

what's the difference between association table and this mapping table ?

Upvotes: 2

Views: 1211

Answers (1)

Ruben Helsloot
Ruben Helsloot

Reputation: 13129

If I understand you correctly, you're asking about the difference between an association table

UserBranches = db.Table(
    'user_branches',
    db.Column('user_id', db.Integer, db.ForeignKey('user.id'), primary_key=True),
    db.Column('branch_id', db.Integer, db.ForeignKey('branch.id'), primary_key=True)
)

and a mapping table

class UserBranch(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user = db.relationship("User", backref=db.backref("UBMuser", lazy="dynamic"))
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
    branch = db.relationship("Branch", backref=db.backref("UBMbranch", lazy="dynamic"))
    branch_id = db.Column(db.Integer, db.ForeignKey('branch.id'))

On a database level, there is no real difference between them. If you only want to have a strict Many To Many relationship between two tables, just use an association table. Because of SQLAlchemy, you never have to do anything with it, because joining through this table happens automatically, as soon as you join Users to Branches or the reverse.

If, however, you want to do more, like have it actually denote a relationship, then the mapping table like you wrote it the way to go, because it behaves exactly like a normal table. This means you can use it like UserBranch.created_at or even query it directly if you want.

Upvotes: 3

Related Questions