Many to Many Relationship

The company can have many employees and a single employee can work for multiple companies. So we have to maintain the many to many relationships between Employee and Company model.

In many to many relationships, we have to create a pivot table to have the primary keys of both tables to be in pivot table as:

company_employee = db.Table('company_employee_association',
                            db.Column('company_id', db.String(), db.ForeignKey('companies.id'), primary_key=True),
                            db.Column('employee_id', db.String(), db.ForeignKey('employees.id'),primary_key=True))

class Employee(db.Model):
    __tablename__ = 'employees'

    def __init__(self,
                 name,
                 age,
                 gender,
                 phone):
        self.id = str(uuid.uuid4())
        self.name = name
        self.age = age
        self.gender = gender
        self.phone = phone

    id = db.Column(db.String(), primary_key=True, unique=True, nullable=False, default=str(uuid.uuid4()))
    name = db.Column(db.String(), nullable=False)
    age = db.Column(db.String(), nullable=False)
    gender = db.Column(db.String(), nullable=False)
    phone = db.Column(db.String(), nullable=False)
    educations = db.relationship('Education', back_populates='employee')
    companies = db.relationship('Company', secondary=company_employee, back_populates='employees')

    def to_dict(self):
        return {
            'name': self.name,
            'age': self.age,
            'gender': self.gender,
            'phone': self.phone
        }

class Company(db.Model):
    __tablename__ = 'companies'

    def __init__(self, name, location):
        self.id = str(uuid.uuid4())
        self.name = name
        self.location = location

    id = db.Column(db.String(), primary_key=True, unique=True, nullable=False)
    name = db.Column(db.String())
    location = db.Column(db.String())
    employees = db.relationship('Employee', secondary=company_employee, back_populates='companies')

In the above example, we can see that to maintain the relationship between two models we have added as the relationship with the attribute secondary=company_employee in both database models employee and company.

We can append the companies of the employee as:

employee = Employee()
company = Company()
employee.companies.append(company)
db.session.add(employee)
db.session.commit()

or We can append the employee on company as:

company = Company()
employee = Employee()
company.employees.append(employee)
db.session.add(company)
db.session.commit()

Implementation details can be found over this commit