1

I am new to SQLAlchemy, I am trying to build a practice project using SQLAlchemy. I have created the database containing tables with the following relationship. Now my questions are :

  1. How to INSERT data into the tables as they are interdependent?
  2. Does this form a loop in database design?

  3. Is the looping database design, a bad practice? How to resolve this if its a bad practice?

    Department.manager_ssn ==> Employee.SSN
    and
    Employee.department_id ==> Department.deptid

database relationship diagram

and following is the current version of code creating this exact database.

# Department table
class Departments(Base):
    __tablename__ = "Departments"   

    # Attricutes
    Dname= Column(String(15), nullable=False)
    Dnumber= Column(Integer, primary_key=True)
    Mgr_SSN= Column(Integer, ForeignKey('Employees.ssn'), nullable=False)
    employees = relationship("Employees")

# Employee table
class Employees(Base):
    __tablename__ = "Employees" 

    # Attributes
    Fname = Column(String(30), nullable=False) 
    Minit = Column(String(15), nullable=False)  
    Lname = Column(String(15), nullable=False)  
    SSN = Column(Integer, primary_key=True)
    Bdate = Column(Date, nullable=False)
    Address = Column(String(15), nullable=False)  
    Sex = Column(String(1), default='F', nullable=False)
    Salary = Column(Integer, nullable=False)
    Dno = Column(Integer, ForeignKey('Departments.Dnumber'), nullable=False)
    departments = relationship("Departments")

Please provide the solution in SQLAlchemy only and not in flask-sqlalchemy or flask-migrate and I am using Python 3.6.

Sunil
  • 429
  • 1
  • 9
  • 25
  • i think you should read this answer about loop relationship, https://stackoverflow.com/a/10458105/4995451 but if you want to keep this structure you can make relationship with `nullable=True`. so you can insert one table before another table – faisal burhanudin Sep 12 '17 at 05:15
  • 1
    Thank you @faisalburhanudin, that did solve my problem. – Sunil Sep 12 '17 at 06:10

1 Answers1

0

You can avoid such circular reference design altogether by

  • Declaring the foreign key constraint on just one side of the relationship
  • Use a boolean flag to denote if the employee is a manager 
class Department(Base):
    __tablename__ = 'departments'

    department_id = Column(Integer, primary_key=True)
    employees = relationship('Employee', lazy='dynamic', back_populates='department')    


class Employee(Base):
    __tablename__ = 'employees'

    employee_id = Column(Integer, primary_key=True)
    is_manager = Column(Boolean, nullable=False, default=False)
    department_id = Column(Integer, ForeignKey('departments.department_id'), nullable=False)

    department = relationship('Department', back_populates='employees')

You can find the manager of the department using

department = session.query(Department).get(..)
department.employees.filter(Employee.is_manager == True).one_or_none()
Ivan Choo
  • 1,997
  • 15
  • 15
  • Although this seems correct, it would add data redundancy at **is_manager** column with too many `False`. Thank you for the solution. I will let the experienced one, to determine if the answer is correct. – Sunil Sep 12 '17 at 07:21
  • IMO the "redundant" column is worth it. A circular table reference is a huge pain to manage. Denormalizing data is not necessarily a bad thing, as it can help in certain query optimization, e.g. `session.query(Employee).filter(Employee.is_manager == True).all()` returns all managers. – Ivan Choo Sep 12 '17 at 07:25
  • If we use this solution, then we can make more than one manager to the same department. Two (or more) records in the **Employee table** can have `is_manager = True` while both(or all) having the **same department_id**. – Sunil Sep 12 '17 at 13:00
  • You can use sqlalchemy's `validate` or ORM events to unset the previous manager when you reassign another. Triggers can be another option. – Ivan Choo Sep 12 '17 at 13:23