sqlalchemy - What's the difference between an association table and a regular table?

Question

I don't think I fully understand association tables. I know how to work with normal tables i.e add rows and what not but I don't understand how to work with an association table.

why would I use the below

    student_identifier = db.Table('student_identifier',
    db.Column('class_id', db.Integer, db.ForeignKey('classes.class_id')),
    db.Column('user_id', db.Integer, db.ForeignKey('students.user_id'))
)

Vs

class studentIdent(db.model):
  db.Column(db.Integer, db.ForeignKey('classes.class_id')),
  db.Column(db.Integer, db.ForeignKey('students.user_id'))

Answer 1

As mentioned in a comment to the question, you would not bother creating a class for the association table if it only contains the foreign keys linking the two tables in the many-to-many relationship. In that case your first example – an association table – would be sufficient.

However, if you want to store additional information about the nature of the link between the two tables then you will want to create an association object so you can manipulate those additional attributes:

class StudentIdent(db.Model):
    __tablename__ = "student_identifier"
    course_id = db.Column(
        db.Integer, 
        primary_key=True, 
        autoincrement=False,
        db.ForeignKey('courses.course_id')
    )
    user_id = db.Column(
        db.Integer, 
        primary_key=True, 
        autoincrement=False,
        db.ForeignKey('students.user_id')
    )
    enrolment_type = db.Column(db.String(20))
    # reason for student taking this course
    #   e.g., "core course", "elective", "audit"

and then you could create the link between a given student and a particular course by creating a new instance of the association object:

thing = StudentIdent(course_id=3, user_id=6, enrolment_type="elective")

Note: This is just a basic linkage. You can get more sophisticated by explicitly declaring a relationship between the ORM objects.