10

I have 3 tables:

  • People
  • Post
  • Likes

When I design the ER model it has a cyclic dependency:

         1:N
People --------< Post

         1:N
Post ----------< Likes

         1:N
People --------< Likes

The logic is:

  • 1 people can have many posts.

  • 1 post has many likes.

  • 1 people can like many posts (created person cannot like his own post).

How can I remove this kind of cyclic design? Or is my db design wrong?

10

Business rules

Let us make some rewordings to the business rules you have presented:

  • A Person creates zero-one-or-many Posts.
  • A Post receives zero-one-or-many Likes.
  • A Person manifests zero-one-or-many Likes, each of wich pertains to one specific Post.

Logical models

Then, from such set of assertions, I have derived the two logical level IDEF1X[1] data models that are shown in Figure 1.

Figure 1 - People and Posts Data Models

Option A

As you can see in the Option A model, PersonId migrates[2] from Person to Post as a FOREIGN KEY (FK), but it receives the role name[3] of AuthorId, and this attribute makes up, together with PostNumber, the PRIMARY KEY (PK) of the the Post entity type.

I assume that a Like can only exist in connection with a particular Post, so I have set up a Like PK that comprises of three different attributes: PostAuthorId, PostNumber and LikerId. The combination of PostAuthorId and PostNumber is a FK that makes the proper reference to the Post PK. LikerId is, in turn, a FK that establishes the suitable association with Person.PersonId.

With the aid of this structure, you ensure that a determined person can only manifest a single Like occurrence to the same Post instance.

Methods to prevent a Post Author from liking his own Post

Since you do not want to allow the possibility that a person can like his/her authored posts, once in the implementation phase, you should establish a method that compares the value of Like.PostAuthorId with the value of Like.LikerId in every INSERT attempt. If said values match, (a) you reject the insertion, if they do not match (b) you let the process continue.

In order to accomplish this task in your database, you can make use of:

  1. A CHECK CONSTRAINT but, of course, this method excludes MySQL, since it has not been implemented in this platform so far, as you can see here and here.

  2. Code lines inside an ACID Transaction.

  3. Code lines within a TRIGGER, which could return a custom message indicating the rule violation attempt.

Option B

If the author is not an attribute that identifies in a primary way a post in your business domain, you could go with a structure similar to the one depicted in Option B.

This approach also makes sure that a post can only be liked by the same person one single time.


Notes

1. Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was defined as a standard in december 1993 by the United States National Institute of Standards and Technology (NIST).

2. IDEF1X defines key migration as “The modeling process of placing the primary key of a parent or generic entity in its child or category entity as a foreign key”.

3. A role name is a denotation assigned to a foreign key attribute in order to express the meaning of such attribute in the context of its corresponding entity type. Role naming is recommended since 1970 by Dr. E. F. Codd in his seminal paper entitled “A Relational Model of Data for Large Shared Data Banks”. For its part, IDEF1X —keeping fidelity regards relational practices— also advocates this procedure.

6

I don't see anything being cyclic here. There are people and posts and two independent relationships between these entities. I would see likes as the implementation of one of these relationships.

  • A person can write many posts, a post is written by one person: 1:n
  • A person can like many posts, a post can be liked by many people: n:m
    The n:m relationship can be implemented with another relation: likes.

Basic implementation

The basic implementation could look like this in PostgreSQL:

CREATE TABLE person (
  person_id serial PRIMARY KEY
, person    text NOT NULL
);

CREATE TABLE post (
  post_id   serial PRIMARY KEY
, author_id int NOT NULL  -- cannot be anonymous
     REFERENCES person ON UPDATE CASCADE ON DELETE CASCADE  -- 1:n relationship
, post      text NOT NULL
);

CREATE TABLE likes (  -- n:m relationship
  person_id int REFERENCES person ON UPDATE CASCADE ON DELETE CASCADE
, post_id   int REFERENCES post ON UPDATE CASCADE ON DELETE CASCADE
, PRIMARY KEY (post_id, person_id)
);

Note in particular that a post must have an author (NOT NULL), while the existence of likes is optional. For existing likes, however, post and person must both be referenced (enforced by the PRIMARY KEY that makes both columns NOT NULL automatically (you might add these constraints explicitly, redundantly) so anonymous likes are also impossible.

Details for the n:m implementation:

Prevent self-like

You also wrote:

(created person cannot like his own post).

That's not enforced in the above implementation, yet. You could use a trigger.
Or one of these faster / more reliable solutions:

Rock-solid for a cost

If it needs to be rock-solid, you could extend the FK from likes to post to include the author_id redundantly. Then you can rule out incest with a simple CHECK constraint.

CREATE TABLE likes (
  person_id int REFERENCES person ON UPDATE CASCADE ON DELETE CASCADE
, post_id   int 
, author_id int NOT NULL
, CONSTRAINT likes_pkey PRIMARY KEY (post_id, person_id)
, CONSTRAINT likes_post_fkey FOREIGN KEY (author_id, post_id)
     REFERENCES post(author_id, post_id) ON UPDATE CASCADE ON DELETE CASCADE
, CONSTRAINT no_self_like CHECK (person_id <> author_id)
);

This requires an otherwise also redundant UNIQUE constraint in post:

ALTER TABLE post ADD CONSTRAINT post_for_fk_uni UNIQUE (author_id, post_id);

I put author_id first to supply a useful index while being at it.

Related answer with more:

Cheaper with a CHECK constraint

Building on the "Basic implementation" above.

CHECK constraints are meant to be immutable. Referencing other tables for a check is never immutable, we are abusing the concept a bit here. I suggest to declare the constraint NOT VALID to properly reflect that. Details:

A CHECK constraint seems reasonable in this particular case, because the author of a post seems like an attribute that never changes. Disallow updates to that field to be sure.

We fake an IMMUTABLE function:

CREATE OR REPLACE FUNCTION f_author_id_of_post(_post_id int)
  RETURNS int AS
'SELECT p.author_id FROM public.post p WHERE p.post_id = $1'
LANGUAGE sql IMMUTABLE;

Replace 'public' with the actual schema of your tables.
Use this function in a CHECK constraint:

ALTER TABLE likes ADD CONSTRAINT no_self_like_chk
   CHECK (f_author_id_of_post(post_id) <> person_id) NOT VALID;
4

I think you are having difficulties figuring this out because of how you state your business rules.

People and Posts are "objects". Like is a verb.

You have really just 2 actions:

  1. A person can create one or more posts
  2. Many persons can like many posts. (a compilation of your last 2 statements)

people like posts diagram

The "likes" table will have person_id and post_id as a primary key.

protected by Paul White says GoFundMonica yesterday

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.