JoinTabelle

JoinTabelle, also called join table or junction table, is a database table used to implement many-to-many relationships between two other tables by storing pairs of foreign keys. It serves as an intermediary that connects related records from the two tables.

Structure and keys: A join table typically contains two (or more) foreign key columns, each referencing the primary key of one of the related tables. Together, these foreign keys usually form a composite primary key, ensuring that each pair appears only once. Some designs use a separate surrogate primary key while still keeping the two foreign keys with a unique constraint. The table may also include additional attributes that describe the relationship, such as timestamps or status fields.

Example: In a university database, a join table named Enrollment connects Students and Courses. It might contain student_id, course_id, enrollment_date, and grade. The primary key is (student_id, course_id), with foreign keys referencing Students(id) and Courses(id).

Design considerations: Key goals are normalization and referential integrity. Indexing the foreign keys improves join performance. Decide between a composite primary key or a surrogate key, and enforce constraints to prevent duplicate relationships.

Variants and usage: Join tables are also called bridging tables or linking tables. In some ORM systems, they are managed automatically; when they carry additional attributes, they are treated as full-fledged entities rather than simple junctions.

See also: many-to-many relationships, foreign keys, relational databases, normalization.