Domain Modeling: Exercise II - solution

You can find the exercise description here.  Below is one possible solution.

This exercise is a good example of a domain modeling a more abstract concept. Our primary model – let's call it Post – doesn't line up precisely with a real-world thing, like Student or Teacher from the previous exercise. Rather, it's a digital analogy for something that could exist in the real world (like a Polaroid photo pinned to a physical cork-board, displayed for the world to see, for instance), and Post is something that only exists in your digital world.

Here are two main entities we came up with to start, based on the current user stories/wireframes:

• User – a person using the system
• Post – the taco image being posted, along with data that describes it, like the time it was posted and the user who posted it

As noted, we are going to ignore the mechanics of how a photo gets uploaded and stored, and simply use a filename for the time being, e.g. tacos.jpg.

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_name TEXT,
  real_name TEXT,
  location TEXT
);

CREATE TABLE posts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER,
  image_file TEXT,
  posted_at TEXT
);

The column names are going to vary based on what you thought they should be called, but generally, this is straightforward. The one thing to note is that there is a one-to-many relationship between User and Post, that is, a User may have one-to-many Posts. As we have learned, in SQL, a one-to-many relationship is built by adding a foreign key column to the "many" side of the equation – in this case, we have a user_id on the posts table.

Likes

Next, we need our domain model to support the ability for a user to "like" a post. This "like count" is then displayed on the post itself; for example, on the wireframe, our example post has 85 likes. Our instinct might be to say, the number of likes is simply an attribute of a post, and implement it by simply adding a column to the posts table:

CREATE TABLE posts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER,
  image_file TEXT,
  posted_at TEXT,
  number_of_likes INTEGER
);

This certainly satisfies our requirement that a Post has likes, and is simply an integer that can be incremented based on the number of people who like the post. However, it does not work when considering the requirement in our fourth user story: As a user, I want to not be able to like a post more than once, so I cannot artificially inflate the popularity of a post.

At some point, we'd need to add some application-level logic that prevents a user from incrementing this number more than once – however, it's pretty difficult to do that unless we know whether or not a user has liked a post before. We can do this once we shift our domain model towards the idea that a like is the join model between a user and a post:

• A User has a many-to-many relationship with a Post
• A many-to-many relationship is actually two one-to-many relationships put together with a join model – in this case, a Like
• A user has one-to-many liked posts
• A post is liked by one-to-many users

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_name TEXT,
  real_name TEXT,
  location TEXT
);

CREATE TABLE posts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER,
  image_file TEXT,
  posted_at TEXT
);

CREATE TABLE likes (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER,
  post_id INTEGER
);

This creates the many-to-many relationship between a user and a post, while also creating a way to track which users have liked which posts.

Comments

A Comment is very similar to a Like. It is another join model, representing the many-to-many relationship between a Post and a User.

• A User has a many-to-many relationship with a Post
• A many-to-many relationship is actually two one-to-many relationships put together with a join model – in this case, a Comment
• A user has one-to-many commented on posts
• A post is commented on by one-to-many users

CREATE TABLE comment (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER,
  post_id INTEGER,
  body TEXT
);

The only difference between a Comment and a Like, in practice, is that we need to add an additional field – body – to hold the body of the comment itself.

Followers

There are a couple of concepts to discuss, in order to properly model one user following another user.

The first is that it's perfectly ok for a domain model relationship to be self-referential. That is, a model can have a relationship with itself, i.e. a user may followed by one-to-many users. This is not super-common, but it is sometimes necessary to do.

The second is that, while we've learned that a foreign key is, by convention, the singular name of a model followed by _id, it is occasionally necessary to break this convention to make the database design more clear. Let's look at the finished Follower model:

CREATE TABLE followers (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  followed_user_id INTEGER,
  follower_user_id INTEGER
);

In this particular case, it's more important to name our columns this way, to make a distinction between the user being followed and the user doing the following. Note that we're still using the word user_id in the names of these columns, to ensure that we continue to know that the foreign keys reference a primary key in the users table.

Here is our complete domain model, implemented using SQL:

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_name TEXT,
  real_name TEXT,
  location TEXT
);

CREATE TABLE posts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER,
  image_file TEXT,
  posted_at TEXT
);

CREATE TABLE likes (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER,
  post_id INTEGER
);

CREATE TABLE comments (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER,
  post_id INTEGER,
  body TEXT
);

CREATE TABLE followers (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  followed_user_id INTEGER,
  follower_user_id INTEGER
);