Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I have 3 tables:
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?
Let us make some rewordings to the business rules you have presented:
Person creates zero-one-or-many Posts.Post receives zero-one-or-many Likes.Person manifests zero-one-or-many Likes, each of wich pertains to one specific Post.Then, from such set of assertions, I have derived the two logical level IDEF1X[1] data models that are shown in Figure 1.
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.
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.
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.
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.
I don't see anything 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.
1:nn:mlikes.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)
);
A post must have an author (NOT NULL), while the existence of any 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 implicitly. So anonymous likes are impossible.
About the n:m implementation:
You 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:
If it needs to be rock-solid, extend the FK from likes to post to include 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 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:
CHECK constraintBuilding on the "Basic implementation" above.
CHECK constraints are meant to be immutable. Referencing another table is never immutable, we are abusing the concept a bit here. Declare the constraint NOT VALID to properly reflect that. See:
A CHECK constraint seems reasonable in this particular case, as 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
LANGUAGE sql IMMUTABLE AS
'SELECT p.author_id FROM public.post p WHERE p.post_id = $1';
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;
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:
The "likes" table will have person_id and post_id as a primary key.
