0

This is regarding to postgres that I was asked to create data model which has a relationship with user.

For example a new data model called Comment M:1 User, but while creating the data model is it possible to set restriction that only 100 commend records can be created? Might not even link them with a PK might be just a pure column with user_id.

Usually if this need to be done, I do it within the RESTful api request to do checks if there's already 100 comments created by the user, but I told to do it on the data model side. I tried googling around but might not be using the correct keywords so a little bit lost here that I want to make sure this is possible with just within one table creation (comment)

Thanks in advance for any suggestions / advices.

3
  • 1
    What you're probably looking for is a Constraint or a Trigger, but this would honestly be a bad design pattern, in my opinion. I think this kind of logic is better managed in the application / API layer.
    – J.D.
    Commented Sep 30, 2021 at 4:25
  • 1
    @J.D. but what happens if a new app starts writing to the db? It won't know anything about the old app's constraints. If you want to ensure a max. of 100 comments full stop, you're better off with trigger!
    – Vérace
    Commented Sep 30, 2021 at 8:17
  • 1
    @Vérace Yea I agree a Trigger is probably the better route of the two, but I still think this is something that should be enforced in the application layer instead, personally.
    – J.D.
    Commented Sep 30, 2021 at 11:50

1 Answer 1

1

Using TRIGGER is a good approach. Also, it's worth considering using ARRAY to manage your comment. For example:

DROP TABLE IF EXISTS tb_customers, tb_comments;

CREATE TABLE tb_customers (
  customer_id uuid primary key, 
  username text
);

CREATE TABLE tb_comments (
  customer_id uuid primary key, 
  comment text[]
);

ALTER TABLE tb_comments ADD CONSTRAINT tb_comments_constr01 
  CHECK ( array_length(comment, 1) <= 10); -- LIMIT 10 comment 

INSERT INTO tb_customers VALUES 
  ('53003bcb-3d1e-4955-96c0-272719e9762d', 'username_01'),
  ('07c01749-5ec2-4d38-9b74-15168fe185f5', 'username_02'),
  ('ab68a33d-ddb0-4bc5-af75-a7d17cb9c157', 'username_03');
 
INSERT INTO tb_comments (customer_id, comment) values ('07c01749-5ec2-4d38-9b74-15168fe185f5', array['$1'])
ON CONFLICT (customer_id) 
DO UPDATE SET comment = array_append(tb_comments.comment, '$1');

INSERT INTO tb_comments (customer_id, comment) values ('ab68a33d-ddb0-4bc5-af75-a7d17cb9c157', array['$2'])
ON CONFLICT (customer_id) 
DO UPDATE SET comment = array_append(tb_comments.comment, '$2');

INSERT INTO tb_comments (customer_id, comment) values ('53003bcb-3d1e-4955-96c0-272719e9762d', array['$3'])
ON CONFLICT (customer_id) 
DO UPDATE SET comment = array_append(tb_comments.comment, '$3');
2
  • 1
    Nice! They may want to add another CHECK (array_ndims(comment) = 1) to restrict the arrays to 1-dimensional only. Commented Oct 1, 2021 at 8:47
  • Thank you so much, I am trying out the trigger since I am new at this, documents seems straight forward and found some samples online but somehow I kept on getting errors :( anyways thank you so much for the suggestion and the sample of Array
    – Dora
    Commented Oct 5, 2021 at 3:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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