MySQL structure for following multiple content types

Question

I'm building a web app that will (ideally) allow users to follow discussion threads (which are in Q&A format like this site), but also follow other content types, such as firms and schools that have profile pages. (The site is intended to help with professional job-seeking, so the back-end provides a bare-bones profile page for firms, schools, etc.)

Would it be more efficient to have one "Follow" table that has a follow_entity_type field that would be access and then redirect to the appropriate content table (Q&A, firms, etc.)? Or should I have a "Follow" table for each content type that need to accessed separately when I try to compile the user's feed? The first seems to entail more complex coding and queries, while the second would make it more difficult to organize the feed chronologically for all types of posts.

I'm sure the solution is straight-forward, but as a part-time developer and autodidact, some times I miss the basics.

Answer 1

I'd take the follow_entity_type field most times. It's in my opinion a lot easier to integrate one extra type of fetch from an entity list then integrate a new join table in all instances of the list. It would also mean only 1 instead of 2 tables would have to be added per 'content type', in which storing & retrieving is already taken care of. The queries don't get that much harder IMHO.

Answer 2

Think of how you'd do this in OO design: you'd have a common superclass or interface for all the types of things that can be followed. Call it Followable.

interface Followable { }

class QandA implements Followable { ... }
class Profiles implements Followable { ... }

Then when you represent a collection of "followable" objects, you ensure the collection consists of objects for which $object instanceof Followable is true.

You can do the same thing with SQL tables:

CREATE TABLE Followables ( follow_id INT AUTO_INCREMENT PRIMARY KEY ... );

CREATE TABLE QandA ( qanda_id INT PRIMARY KEY ... , 
  FOREIGN KEY (qanda_id) REFERENCES Followables(follow_id));
CREATE TABLE Profiles ( profile_id INT PRIMARY KEY ... , 
  FOREIGN KEY (profile_id) REFERENCES Followables(follow_id));

Now your references to things the user follows are a foreign key to Followables:

CREATE TABLE UserFollows (
  user_id INT NOT NULL,
  follow_id INT NOT NULL,
  PRIMARY KEY (user_id, follow_id),
  FOREIGN KEY (user_id) REFERENCES Users(user_id),
  FOREIGN KEY (follow_id) REFERENCES Followables(follow_id)
);

See also Class Table Inheritance.