Intermediate Phase Five - Join Table & Associations

Understand the Problem

We have a many-to-many relationship (Trees & Insects). We need a join table InsectTrees with foreign keys referencing Insects and Trees. We must set up the Sequelize model, migration, and belongsToMany associations in both Insect and Tree models.

Devise a Plan

1. Use Sequelize CLI to create the InsectTree model with fields insectId and treeId.
2. Add references to the foreign keys, ensuring ON DELETE CASCADE.
3. Migrate the database. Verify table creation in SQLite.
4. Update Insect and Tree models with belongsToMany associations through InsectTree.

Carry Out the Plan (Solution)

File Names & Locations:

• server/models/insecttree.js
• server/migrations/xxxx-create-insecttree.js
• server/models/insect.js and server/models/tree.js

// Example of belongsToMany in tree.js
// Tree.belongsToMany(models.Insect, {
//   through: models.InsectTree,
//   foreignKey: 'treeId',
//   otherKey: 'insectId'
// });

// in insect.js
// Insect.belongsToMany(models.Tree, {
//   through: models.InsectTree,
//   foreignKey: 'insectId',
//   otherKey: 'treeId'
// });
    

Expected Input & Output: Not directly relevant yet—this sets up the table and relationships for future data retrieval and insertion. You’ll confirm success by either seeding or checking the schema.

Elementary (Basic) Approach

• Use default timestamps and a minimal approach for the join table (insectId, treeId).

Advanced Approach

• Include an extra attribute in the join table if you need to track more details about each association (e.g., date spotted).

Step-by-Step Directions:

1. Generate model & migration: npx sequelize-cli model:generate.
2. Edit the migration to add foreign key constraints with references and onDelete.
3. Migrate: npx sequelize-cli db:migrate.
4. In insect.js and tree.js, add belongsToMany associations referencing the join model.

Explanations & Analogies: A join table is like a sign-up sheet linking participants (insects) to events (trees). Each row says which insect was found near which tree, but insects and trees can link to many of each other.

Real World Example: A many-to-many: multiple students can enroll in multiple classes, tracked in an enrollment table.

Look Back

Check the table structure in SQLite. Confirm your associations in code have no errors. Prepare for seeding or querying in subsequent phases.