Mysql – How to avoid a cyclic dependency (circular reference) between 3 tables

database-designdesign-patternMySQLpostgresql

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?

Best Answer

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:

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.
Code lines inside an ACID Transaction.
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.}

Related Solutions

MySQL – Structure a Database for a Blog

Tips:

If an avatar is an image, use BLOB, not TEXT.
Don't prefix each column name with the table name; it clutters unnecessarily.
You are unlikely to ever need BIGINT; use INT UNSIGNED instead (max or 4 billion).
A PRIMARY KEY is a UNIQUE key is a KEY. So don't re-index the PK.
Consider using ENUM for things like status and type.
A Post has both an article and a content? What's up?
IP addresses, if kept as strings, can be VARCHAR(39) CHARACTER SET ascii.

Database Design – Modeling Relationships with Variable Tables

There seem to be 2 main ways to model such a situation:

(1) Use supertype/subtypes. However, as we are not discussing "object oriented" design, the second option may be a bit more appropriate.

(2) Use a so-called ARC, which expresses a XOR relationship.

Suppose we have an entity TRANSACTION. Each customer can be involved in one ore more transactions AND a customer is either a COMPANY or a PERSON (XOR). Our ERD/relational model could look something like the ones below. Notice the "arc", that is drawn across both relationships.

As for the implementation: you can code a CHECK constraint (in order to avoid triggers), which will enforce the XOR (exclusive or). The complete DDL code looks something like this (notice the CHECK):

CREATE TABLE company ( 
  c_id NUMBER PRIMARY KEY
);

CREATE TABLE person ( 
  p_id NUMBER PRIMARY KEY
);

CREATE TABLE transaction ( 
  t_id NUMBER PRIMARY KEY
, c_id NUMBER 
, p_id NUMBER
);

ALTER TABLE transaction 
ADD CONSTRAINT fkarc CHECK (
     ( (c_id IS NOT NULL) AND (p_id IS NULL) ) 
  OR ( (p_id IS NOT NULL) AND (c_id IS NULL) ) 
);

ALTER TABLE transaction
ADD CONSTRAINT transaction_company_fk 
FOREIGN KEY ( c_id ) REFERENCES COMPANY ( c_id ) ;

ALTER TABLE transaction
ADD CONSTRAINT transaction_person_fk
FOREIGN KEY ( p_id ) REFERENCES PERSON ( p_id ) ;

(Tested on Oracle 12c)