Sequelize 2: Relationships and Data Modeling#

To download the demo code for this lecture, run:

$ dmget wb-sequelize-2 --demo

Then, cd into the project directory and install the dependencies with npm install.

You will need to create four databases using createdb. These are employees, captains, comments, and genres.

Goals#

Discuss data modeling choices
Go over types of relationships between tables and how to implement them

Normalization#

The Problem#

We need to store contact information in our database
However, some people have many phone numbers

Solution 1: Many Rows#

user_id	fname	lname	age	phone
1	Jane	Jay	26	415 555 1212
2	Jane	Jay	26	415 555 1213
3	Nick	Nest	43	415 555 2000

What are potential problems with this?

One person can have multiple user_id’s
Same person or two people with same name & age?

Solution 2: Many Columns#

user_id	fname	lname	age	phone1	phone2	…
1	Jane	Jay	26	415 555 1212	415 555 1213
2	Nick	Nest	43	415 555 2000	null

What are potential problems with this?

Don’t know how many columns we’ll need
Queries could be horrible: SELECT phone1, phone2, ...

Two Primary Sins of Data Modeling#

Repeating dependencies (for example, storing Jane’s name and age twice)
Representing multiple data for the same category in columns (phone1, phone2, …, phoneInfinity)

This lecture will address how to avoid these sins!

Normalization: In Short#

“Every piece of information lives in exactly one place and relates directly to the key.”

Review: One-To-Many#

One-To-Many Relationships#

One To Many

Department.hasMany(Employee, { foreignKey: 'deptCode' });
// creates Department.getEmployees(), Department.addEmployee()

Employee.belongsTo(Department, { foreignKey: 'deptCode' });
// creates Employee.getDepartment(), Employee.setDepartment()

const firstEmp = await Employee.findOne();
console.log(await firstEmp.getDepartment());
// { deptCode: 'legal', deptName: 'Legal', phone: '555-2222' }

const mktg = await Department.findByPk('mktg');
console.log(await mktg.getEmployees());
// [{ id: 3, name: 'Maggie', state: 'UT', salary: 70000, deptCode: 'mktg'}]

Using Setter Methods#

To associate an employee with a department, you can simply pass in the foreign key:

const liz = await Employee.create(
  { name: 'Liz', deptCode: 'legal', salary: 100000 });

Sometimes that isn’t very convenient (especially with auto-incrementing keys). So instead you can use the setter methods that Sequelize provides.

const liz = await Employee.create({ name: 'Liz', salary: 100000 });
const legal = await Department.create(
  { deptCode: 'legal', deptName: 'Legal', phone: '555-2222' });

liz.setDepartment(legal);  // or legal.addEmployee(liz);

Phone Directory#

We want to show a phone directory of employees and their department.

Name	Department	Phone
Liz	Legal	555-2222
Leonard	Legal	555-2222
Maggie	Marketing	555-9999
Nadine	-	-

async function showPhoneDirectory() {
  const emps = await Employee.findAll();

  // Can't use await inside a for loop, so use forEach
  emps.forEach(async (emp) => {
    const dept = await emp.getDepartment();
    if (dept) {
      console.log(emp.name, dept.deptName, dept.phone);
    } else {
      console.log(emp.name, '-', '-');
    }
  });
}

However this is somewhat inefficient because Sequelize fires off several queries:
- one for the list of employees
- one to get each department

Phone Directory with Eager Loading#

async function showPhoneDirectoryEager() {
  const emps = await Employee.findAll({ include: Department });
  // now the department will come with each employee!

  for (const emp of emps) {
    const dept = emp.department;
    if (dept) {
      console.log(emp.name, dept.deptName, dept.phone);
    } else {
      console.log(emp.name, '-', '-');
    }
  }
}

This will just perform one SQL query (using a JOIN) to get all information for employees and their departments in advance.

One-To-One Relationships#

One To One

Ship.hasOne(Captain, { foreignKey: 'shipId' });
// creates Ship.getCaptain(), Ship.setCaptain()

Captain.belongsTo(Ship, { foreignKey: 'shipId' });
// creates Captain.getShip(), Captain.setShip()

Using One-To-One Relationships#

const blackPearl = await Ship.create(
  {name: "Black Pearl", type: "pirate ship"});

const jack = await Captain.create(
  {name: "Jack Sparrow", nationality: "England", skillLevel: 10});

jack.setShip(blackPearl); // or blackPearl.setCaptain(jack);
console.log(await jack.getShip());

Middle Tables#

Many-to-Many: A Lie?#

In our books database, users can comment on books.

Can a user comment on many books?
Can a book be commented on by many users?

Fake Many to Many

Users can have many books, books can have many users…?

Middle Tables#

This may seem like a many-to-many relationship. It’s not.

There’s no actual relationship between books and users.
Need a middle table (a comments table) to connect them
In reality, this is two one-to-many relationships
- A book can have many comments (by different users)
- A user can have many comments (on different books)

Middle Table

Book.hasMany(Comment, { foreignKey: 'bookId' });
Comment.belongsTo(Book, { foreignKey: 'bookId' });

User.hasMany(Comment, { foreignKey: 'userId' });
Comment.belongsTo(User, { foreignKey: 'userId' });

Remember, the foreign key always goes on the “many” side (here, the Comments table)

Many-To-Many Relationships#

In our books database, books have genres

Can a book have many genres?
Can a genre have many books?

What do we call the middle table?
- No obvious name like “comments”
- Often use combination of table names: BookGenre

Many to Many

The first diagram is not possible to implement in SQL.

We need a table in between – even though there will be no meaningful data in it.

Middle Table vs. Association Table#

Middle table (example: Comment)
- Middle tables have meaningful fields
Association table (example: BookGenre)
- No meaningful fields
- Simply a connector between books and genres
Database can’t tell the difference between association and middle tables
- Design decision
- Treat relationship differently in Sequelize

Association Tables#

Like cables: necessary to connect things together, but we don’t want to see them so we hide them in the back
In our last example, we created Comments and queried for Comments
We do not want to create BookGenres or query for BookGenres directly

This code will automatically create a BookGenres table with foreign keys to Book and Genre.

Genre.belongsToMany(Book, { through: 'BookGenres' });
Book.belongsToMany(Genre, { through: 'BookGenres' });

It will also automatically create two methods: Book.addGenre() and Genre.addBook().
We don’t need to specify foreign key names, because we won’t be using the FKs directly.

Let’s use these addBook and addGenre methods:

const lotr = await Book.create({ title: 'Lord of the Rings' });
const treasureIsland = await Book.create({ title: 'Treasure Island' });
const fantasy = await Genre.create({ name: 'Fantasy' });
const adventure = await Genre.create({ name: 'Adventure' });

await lotr.addGenre(fantasy);
await lotr.addGenre(adventure);
await adventure.addBook(treasureIsland);

console.log(await adventure.getBooks());
// [Lord of the Rings, Treasure Island]

console.log(await lotr.getGenres());
// [Fantasy, Adventure]

If you go into psql and run the command SELECT * FROM bookgenres, you will also notice 3 entries in the BookGenres table.

We didn’t create them directly, they were created automatically when we associated the books with their genres!

Summary#

One-To-Many#

Creating the relationship:

Department.hasMany(Employee, { foreignKey: 'deptCode' });
Employee.belongsTo(Department, { foreignKey: 'deptCode' });

Associating employees with departments:

dept1.addEmployee(emp1);
emp2.setDepartment(dept2);

Getting associated employees/departments:

dept.getEmployees();  // returns array of employees
emp.getDepartment();  // returns a department

One-To-One#

Creating the relationship:

Ship.hasOne(Captain, { foreignKey: 'shipId' });
Captain.belongsTo(Ship, { foreignKey: 'shipId' });

Associating employees with departments:

capt1.setShip(ship1);
ship2.setCaptain(capt2);

Getting associated ship/captain:

capt.getShip();     // returns a ship
ship.getCaptain();  // returns a captain

Many-To-Many#

Creating the relationship:

Genre.belongsToMany(Book, { through: 'BookGenres' });
Book.belongsToMany(Genre, { through: 'BookGenres' });

Associating books with genres:

book1.addGenre(genre1);
genre2.addBook(book2);

Getting associated books/genres:

book.getGenres();  // returns array of genres
genre.getBooks();  // returns array of books