title: Can't figure out how to link multiple nesting many to many relationships
body: <p>I'm setting up a user authentication modules to specify three roles: 'r', 'w', 'x', however I can't figure out how to link all the subcomponents of the website. The website has five levels: 1- Multiple organization 2-Each organization has a number of projects 3-Each project has a number of modules. The user permissions should be linked to the module of the project of the organization.</p>
  
  <p>This is using flask sqlalchmey and flask-migrate. I've read through both flask sqlalchemy, and sqlalchemy.com documentation and found association tables to be potentially what can solve the problem. But I'm stuck here,</p>
  
  <pre><code>class Hospital(db.Model):
  
      __tablename__ = 'hospitals'
      id = db.Column(db.Integer(), primary_key=True)
      name = db.Column(db.String(), unique=True)
  
  
  class Project(db.Model):
  
      __tablename__ = 'projects'
      id = db.Column(db.Integer(), primary_key=True)
      name = db.Column(db.String(), unique=True)
  
  
  class Module(db.Model):
  
      __tablename__ = 'modules'
      id = db.Column(db.Integer(), primary_key=True)
      name = db.Column(db.String(), unique=True)
  
  
  class HospitalProject(db.Model):
  
      __tablename__ = 'hospital_projects'
      id = db.Column(db.Integer(), primary_key=True)
      hospital_id = db.Column(db.Integer, db.ForeignKey('hospitals.id'), primary_key=True)
      project_id = db.Column(db.Integer, db.ForeignKey('projects.id'), primary_key=True)
      hospital = db.relationship('Hospital', backref='projects')
      project = db.relationship('Project', backref='hospitals')
  
  
  class HospitalProjectModules(db.Model):
  
      __tablename__ = 'hospital_project_modules'
      id = db.Column(db.Integer(), primary_key=True)
      name = db.Column(db.String(), unique=True)
      hospital_project_id = db.Column(db.Integer, db.ForeignKey('hospital_projects.id'), primary_key=True)
      module_id = db.Column(db.Integer, db.ForeignKey('modules.id'), primary_key=True)
      hospital_project = db.relationship('HospitalProject', backref='modules')
      module = db.relationship('Module', backref='hospital_projects')
  
  
  class Role(db.Model):
  
      __tablename__ = 'roles'
      id = db.Column(db.Integer(), primary_key=True)
      name = db.Column(db.String(), unique=True)
  
  
  class HospitalProjectModulesUserRoles(db.Model):
  
      __tablename__ = 'hospital_project_modules_user_roles'
      id = db.Column(db.Integer(), primary_key=True)
      hospital_project_module_id = db.Column(db.Integer, db.ForeignKey('hospital_project_modules.id'), primary_key=True)
      role_id = db.Column(db.Integer, db.ForeignKey('roles.id'), primary_key=True)
      user_id = db.Column(db.Integer, db.ForeignKey('users.id'), primary_key=True)
      hospital_project_module = db.relationship('HospitalProjectModules', backref='User_Roles')
      user = db.relationship('User', backref='hospital_project_module_roles')
      role = db.relationship('Role', backref='hospital_project_module_users')
  </code></pre>
  
  <p>I'm running into this error. I know this is because hospital_projects doesn't have a unique identifier but I can't figure out how to solve it.  </p>
  
  <p>sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidForeignKey) there is no unique constraint matching given keys for
  referenced table "hospital_projects"</p>
  
  <pre><code>[SQL:
  CREATE TABLE hospital_project_modules (
          id INTEGER NOT NULL,
          name VARCHAR,
          hospital_project_id INTEGER NOT NULL,
          module_id INTEGER NOT NULL,
          PRIMARY KEY (id, hospital_project_id, module_id),
          FOREIGN KEY(hospital_project_id) REFERENCES hospital_projects (id),
          FOREIGN KEY(module_id) REFERENCES modules (id),
          UNIQUE (name)
  
  app/models.py61:51
  LFUTF-8Pythongit+
  DAMPublishGitHubGit (8)2 updates
  </code></pre>