CODEWITHSUNDEEP
pythondatabase-designmodelsqlalchemyrelationship
Aufwind
Aufwind

Reputation: 26288

Modeling a database many to many relationship between three tables using sqlalchemy in python

Let's consider the following table models for sqlalchemy in python.

class Worker(Base):
    id Column(Integer, primary = True)
    name Column(String, nullable = False)

class Project(Base):
    id Column(Integer, primary = True)
    name Column(String, nullable = False)

class Rating(Base):
    id Column(Integer, primary = True)
    description Column(String, nullable = False)

Let's consider some restrictions on these tables. (Don't question the sense of this example please, it is the best I came up with to describe what I'd like to learn. :-))

  1. A worker may have multiple projects to work on.
  2. A project may have multiple workers assigned to work on it.
  3. For each tuple (worker, project) you have a distinct rating

I understand, that I have three many to many relationship. Those are:

  1. worker and project
  2. worker and rating
  3. rating and project

I have no problems defining a many to many relationship between two tables. I just would have to add the folowing association table and a realtionship to my models.

worker_project = Table('worker_project', METADATA,
                       Column('worker_id', Integer, ForeignKey('workers.id')),
                       Column('project_id', Integer, ForeignKey('projects.id')),

class Worker(Base):
    # [...]
    papers = relationship('Project',
                          secondary=worker_project,
                          backref='workers')

What I do fail in, is to link the table "ratings" like described above. Any help is appreciated.

Upvotes: 3

Views: 1937

Answers (1)

Steinthor.palsson
Steinthor.palsson

Reputation: 6506

Make the ratings table something like this.

  Rating
---------------------
  rating_id (primary key) 
  worker_id (foreign key - worker(worker_id)) 
  project_id (foreign key - project(project_id)) 
  description

If I'm understanding this correctly that is.

Upvotes: 4

Related Questions