To get started with the implementation, we first generate a Relationship model as follows:

Since we will be finding relationships by follower_id and by followed_id, we should add an index on each column for efficiency, as shown in Listing 11.1.

Listing 11.1 also includes a composite index that enforces uniqueness of pairs of (follower_id, followed_id), so that a user can’t follow another user more than once:

  (Compare to the email uniqueness index from Listing 6.22.) As we’ll see starting in Section 11.1.4, our user interface won’t allow this to happen, but adding a unique index arranges to raise an error if a user tries to create duplicate relationships anyway (using, e.g., a command-line tool such as curl). We could also add a uniqueness validation to the Relationship model, but because it is always an error to create duplicate relationships, the unique index is sufficient for our purposes.

    To create the relationships table, we migrate the database and prepare the test database as usual:

The result is the Relationship data model shown in Figure 11.8.