Model Relationships

Many-to-Many Junction Table for Models

How to Find the Many-to-Many Junction Table for Model Relationships

1. Find the junction model identifier corresponding to the junction table through the model's field configuration, as shown in the figure.
2. Search the database table using the junction model identifier, as shown in the figure.- Note: Tables whose names end with "-preview" are junction tables for many-to-many relationships of preview-state models, while tables without the "-preview" suffix are junction tables for many-to-many relationships of formal-state models.

Field Descriptions for the Many-to-Many Junction Table of Models

• leftRecordId stores the record id of the main table (left table), as shown in the figure below: leftRecordId stores the value corresponding to the primary key _id of the student table.
• rightRecordId stores the record id of the subtable (right table), as shown in the figure below: rightRecordId stores the value corresponding to the primary key _id of the course schedule table.

Three Logical Column Types:

1. One-to-Many Association

   • Description: A record in the current model is associated with multiple records in another model.
   • Characteristics: Not physically stored in the table, but retrieved through LEFT JOIN queries.
   • Example: One "class" is associated with multiple "students". In the class table, the one-to-many field does not physically exist.

2. Many-to-Many Association

   • Description: Multiple records in the current model are associated with multiple records in another model.
   • Characteristics: Not physically stored in the table, but retrieved through a junction table and LEFT JOIN queries.
   • Example: The course enrollment relationship between "students" and "courses". In the student table and course table, the many-to-many field does not physically exist.

3. One-to-One Reverse Association

   • Description: A special case of one-to-many relationships, implemented as one-to-one through unique index constraints.
   • Characteristics: Not physically stored in the table, but retrieved through LEFT JOIN queries.
   • Example: The one-to-one relationship between "student ID" and "student". In the student ID table, the one-to-one reverse association field does not physically exist.

Operation Instructions for Foreign Key Constraints in Model Relationships

Model relationships currently do not support foreign key operations. To add foreign key constraints for model relationships, you need to operate on the database table management page in the console. The specific operation instructions are as follows:

Foreign Key Constraints for One-to-One Relationships

Scenario Example

• User ↔ User Profile (one user corresponds to one user profile)
• Student ↔ Student ID Card (one student corresponds to one student ID card)

Graphical Console Operation Method

1. Find the table name and relationship field identifier through the model, as shown in the figure below:
2. Search for relevant tables on the Database Table Management Page and add foreign keys, as shown below:

Example: Creating a Foreign Key Constraint in sql

-- User Profile Table (child table)
CREATE TABLE user_profile (
    _id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE, -- UNIQUE constraint ensures a one-to-one relationship
    avatar VARCHAR(255),
    bio TEXT,
    CONSTRAINT fk_profile_user_id FOREIGN KEY (user_id) 
        REFERENCES user(_id) ON DELETE CASCADE
);

Key Points

• Add a UNIQUE constraint on the foreign key field of the child table
• Use ON DELETE CASCADE to ensure cascading deletion
• The foreign key field must be NOT NULL

Foreign Key Constraints for One-to-Many Relationships

Scenario Example

• Department to Employees (where one department has multiple employees)
• Article to Comments (where one article has multiple comments)

Graphical Console Operation Method

1. Find the table name and relationship field identifier through the model, as shown in the figure below:
2. Search for relevant tables on the Database Table Management Page and add foreign keys, as shown below:

Example: Creating a Foreign Key Constraint in sql

-- Employee Table (the "many" side)
CREATE TABLE employee (
    _id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department_id INT NOT NULL,
    position VARCHAR(100),
    _openid VARCHAR(64),
    CONSTRAINT fk_employee_department_id FOREIGN KEY (department_id) 
        REFERENCES department(_id) ON DELETE CASCADE
);

Key Points

• Foreign key constraints are established on the "many" side
• The foreign key field is usually NOT NULL
• Use ON DELETE CASCADE to handle cascading deletion

Foreign Key Constraints for Many-to-Many Relationships

Scenario Example

• Student ↔ Course (one student takes multiple courses, and one course has multiple students)
• User ↔ Role (one user has multiple roles, and one role is assigned to multiple users)

Graphical Console Operation Method

1. Find the junction model identifier corresponding to the junction table through the model's field configuration, as shown in the figure.
2. Search for the related table on the database table management page using the junction model identifier, as shown in the figure.

Example: Creating a Foreign Key Constraint in sql

-- Junction table (association table)
CREATE TABLE mid_55ZTLScHD (
    _id INT AUTO_INCREMENT,
    rightRecordId INT NOT NULL,
    leftRecordId INT NOT NULL,
    _openid VARCHAR(64),
    PRIMARY KEY (id, rightRecordId, leftRecordId), -- composite primary key
    CONSTRAINT fk_sc_student_id FOREIGN KEY (leftRecordId) 
        REFERENCES student(_id) ON DELETE CASCADE,
    CONSTRAINT fk_sc_course_id FOREIGN KEY (rightRecordId) 
        REFERENCES course(_id) ON DELETE CASCADE
);

Key Points

• The junction table contains two foreign key fields
• Use a composite primary key to ensure the uniqueness of associations
• Both foreign keys are set to cascade on delete

Best Practices for Foreign Key Constraint Operations

1. Model Identifier and Table Naming Rules

• Preview-state table name: model identifier + '-preview' Formal-state table name: model identifier

2. Constraint Naming Conventions

• Use meaningful constraint names: fk_table_column
• Maintain naming consistency to facilitate maintenance

3. Cascade Operation Selection

• CASCADE: When a record in the primary table is deleted, related records in the secondary table are also deleted
• SET NULL: When a record in the primary table is deleted, the foreign key field in the child table is set to NULL.
• RESTRICT: Prevents deletion of primary table records referenced by foreign keys
• NO ACTION: Similar to RESTRICT, but with different check timing

4. Index Optimization

• Create indexes for foreign key fields to improve query performance
• Consider using composite indexes to optimize multi-field queries.

Common Issues and Solutions

Issue 1: Circular Reference

Scenario: Two tables reference each other, forming a circular dependency Solution:

• Use the SET NULL cascade operation
• Handle circular references in the business logic
• Consider using soft delete instead of physical delete

Issue 2: Performance Issues

Scenario: Foreign key constraints impact the performance of large-scale data operations. Solution:

• Perform batch operations during off-peak hours
• Temporarily disable foreign key constraints (use with caution)
• Optimize index policies

Issue 3: Data Inconsistency

Scenario: Foreign key constraints are bypassed, resulting in data inconsistency. Solution:

• Regularly perform data integrity checks
• Use database transactions to ensure operation atomicity
• Implement data validation at the business logic level

Summary

Foreign key constraints are the core mechanism for maintaining the integrity of database relationships. By properly designing foreign key constraints, you can:

1. Ensure data consistency and validity
2. Simplify maintenance operations for related data
3. Improve the accuracy of data queries
4. Reduce the complexity of business logic

In practical applications, appropriate foreign key constraint policies should be selected based on specific business scenarios to balance the requirements of data integrity and system performance.