Refactor (simplify) example tables to only have one relationship table

[nelsonic]

Right now in the basic example #51 we have three tables which are very similar: stars, members and followers. Here is the current ER diagram:

The SQL (CREATE script) looks very repetitive to me:

CREATE TABLE IF NOT EXISTS "followers" (
  "inserted_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "follower_id" INT REFERENCES people (id),
    CONSTRAINT "followers_fk0"
    FOREIGN KEY ("follower_id")
    REFERENCES people (id),
  "leader_id" INT REFERENCES people (id),
    CONSTRAINT "followers_fk1"
    FOREIGN KEY ("leader_id")
    REFERENCES people (id)
);

CREATE TABLE IF NOT EXISTS "members" (
  "inserted_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "person_id" INT REFERENCES people (id),
    CONSTRAINT "members_fk0"
    FOREIGN KEY ("person_id")
    REFERENCES people (id),
  "org_id" INT REFERENCES orgs (id),
    CONSTRAINT "members_fk1"
    FOREIGN KEY ("org_id")
    REFERENCES orgs (id)
);

CREATE TABLE IF NOT EXISTS "stars" (
  "inserted_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "person_id" INT REFERENCES people (id), -- only people can "star" repos.
    CONSTRAINT "stars_fk0"
    FOREIGN KEY ("person_id")
    REFERENCES people (id),
    "repo_id" INT REFERENCES repos (id),
    CONSTRAINT "stars_fk1"
    FOREIGN KEY ("repo_id")
    REFERENCES repos (id)
);

Given the similarities between I'm considering refactoring them into one table relationships:

CREATE TABLE IF NOT EXISTS "relationships" (
  "inserted_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "person_id" INT REFERENCES people (id) DEFAULT NULL,
  "leader_id" INT REFERENCES people (id) DEFAULT NULL,
  "org_id" INT REFERENCES orgs (id) DEFAULT NULL,
  "repo_id" INT REFERENCES repos (id) DEFAULT NULL
);

Note: I've removed the FOREIGN KEY CONSTRAINT because they don't add value right now.

I don't know if that will make it more complex or simplify it. 🤔 🤷‍♂️

The advantage of having fewer tables is that we can abstract the queries 🔍 and show people a general purpose relationships table. 💡 The downside of a single relationships table is that it might "confuse" some beginners ... 😕

The way I'm planning this example in my mind is that we will build the app progressively. ♻️ In the beginning we only need two tables: people and relationships; that's it! 🥇

Todo

• [x] refactor the stars, members and followers tables into a single relationships table. 💻

[nelsonic]

Refactored:

This is what it looks like when we store the list of people who have "starred" a repository:

I'm still a tiny bit "sceptical" of this as it could "confuse" some beginners ... 💭

[nelsonic]

I'm satisfied with this refactor as it reduced the number of tables and introduces a general purpose relationship table which simplifies the code. 👍 If someone reading this tutorial gives us negative feedback (e.g: "you lost me at this point"...) 😕 for the single relationships table, I'm happy to split out the table into multiple tables. 🔙 For now, I consider this done. ✅

[nelsonic]

All three types of relationships are now represented:

[nelsonic]

Latest ER Diagram:

I think I'm "happy" with this now (as a starting point...) 👍