Answer a question

I'm running node.js with an express.js server and sequelize.js. This is an API-only application, so no views written in node.

I'm trying to understand how to architect associations between two data models in PostgreSQL. I have Users and Campaigns, each is their own table with columns concerning each (like 'username' and 'email' for Users, and 'name' and 'message' for Campaigns). Users can own many Campaigns; Users can join many Campaigns. Campaigns can be owned by many Users; Campaigns can have many Users join it.

I imagined I would include two additional tables to define the relationships between Users and Campaigns, one called 'Registrations' which tells which user has joined what, and another called 'CampaignOwners' which tells which user has managerial roles over what Campaign. These two tables each have UserId and CampaignId columns to define the relationship. So you can expect the same UserId and CampaignId to show up in many rows in these tables.

How in the sequelize schema would I define the relationship between Users and Campaigns? I'm checking these docs (http://sequelizejs.com/docs/latest/associations#block-2-line-0), but I'm in unfamiliar territory and would appreciate if someone had the time to explain conceptually how you do this sort of thing using sequelize. For example, I can read and write to the Registrations table, but I've done that by modeling it as a separate DB object, and it has it's own API endpoint ('/api/register/' and pass in the UserId and CampaignId). So now I have a model for Users, Campaigns, and Registrations. It works but seems wrong to me so I'm looking for clarification.

I'm welcoming suggestions about concepts I'm missing, methods I'm missing, documentation I should look at...

Updates

Sequelize's .hasMany and.belongsTo Fiddling with these methods, I'm figuring out how to create new "join tables" and the methods that area created from defining these relationships. (This is my first exposure to an ORM).

Answers

(I'll wait a few hours to see if someone else answers with better information before marking my own answer as correct).

After some research I found the information I need to move forward.

(These two documents were a little hard to digest, but reading over them several times gave the answers I sought).
https://github.com/sequelize/sequelize/wiki/API-Reference-Associations http://sequelizejs.com/docs/latest/associations#check-associations

Concerning architecture: Instead of creating DB models for Users, Campaigns, Registrations and CampaignOwners, just create models for Users and Campaigns. The tables that store the relationship between Users and Campaigns will be created automatically as Join tables (new term for me) when defining the association. The way I'm currently defining that association is like this:

// 'db' is an object that stores all the db models

  // User joins many campaigns. Campaign has many registered users.
db["User"].hasMany(db["Campaign"], { as: "Registration", through: "Registrations" });
db["Campaign"].hasMany(db["User"], { as: "Registration", through: "Registrations" });

  // Campaign has many owners (as users). Users own many campaigns.
db["Campaign"].hasMany(db["User"], { as: "Owner", through: "CampaignOwners" });
db["User"].hasMany(db["Campaign"], { as: "Owns", through: "CampaignOwners" });

Concerning magical methods: When a user creates a campaign, I can register that user as the campaign owner like in the code below. (What's below looks messy to me. I'm sure there's a better way to organize these functions). The functions 'getOwns' and 'addOwner' help clarify for me what an ORM does and how it fits into the Sequelize way of modeling a DB. The names 'addOwner' and 'getOwns' are defined by the 'as' property of the .hasMany relationships defined above.

db.Campaign.create({
        name: req.body.name,
        target: req.body.target,
        callToAction: req.body.callToAction
      })
        .success(function(campaign){
          user
            .save()
            .complete(function(err, owner){
               // addOwner(), a method magically created by Sequelizer, will insert objects into the DB as owners of this campaign
               campaign
                   .addOwner(owner)
                   .success(function(campaignOwner){
                       campaignOwner
                          .getOwns()
                          .success(function(owns){
                             // getOwns(), a method magically created by Sequelizer, gets all campaigns owned by this user and returns it as the 'owns'
                             res.send(owns);
              })
            })
          })
        })