Document types like json
or jsonb
(or xml
or hstore
) are convenient to store, well, documents. Ideal for data with varying keys and rarely updated and not-too-complex filter criteria in queries. Manipulating big documents a lot inside the database would be an anti-pattern.
Structured data that is often written in small increments, and probably searched a lot (like in your case most likely) are much more efficient with a normalized design - regarding storage, performance, concurrent write access and data integrity. Hence the advice in the manual:
JSON data is subject to the same concurrency-control considerations as
any other data type when stored in a table. Although storing large
documents is practicable, keep in mind that any update acquires a
row-level lock on the whole row. Consider limiting JSON documents to a
manageable size in order to decrease lock contention among updating
transactions. Ideally, JSON documents should each represent an atomic
datum that business rules dictate cannot reasonably be further
subdivided into smaller datums that could be modified independently.
Implement your n:m relationship with one ore more junction tables. You can enforce data integrity with constraints (PK, FK, UNIQUE, CHECK, ..), none of which is easily possible with document types. I would lean towards a single table unless you have substantially differing requirements for "like", "bookmark" etc.
Example
If you are unsure about the typical layout, here are the basics:
Assuming that each of your relation types can only be used once per user/doc combination. For just a hand full of values (3 in your case) I would use a 1-byte "char"
column as PK of a lookup table to optimize storage and performance in tables and indexes.
CREATE TABLE reltype (
reltype "char" PRIMARY KEY
, relation_type text UNIQUE NOT NULL
);
INSERT INTO reltype(reltype, relation_type) VALUES
('l', 'like')
, ('b', 'bookmark')
, ('s', 'save');
CREATE TABLE user_doc (
user_doc_id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY
, user_id int REFERENCES users ON UPDATE CASCADE ON DELETE CASCADE
, document_id int REFERENCES documents ON UPDATE CASCADE ON DELETE CASCADE
, reltype "char" REFERENCES reltype NOT NULL DEFAULT 'l'
, CONSTRAINT user_document_pkey UNIQUE(user_id, document_id, reltype)
);
You can always export aggregated data as JSON documents. Even have a VIEW
or MATERIALIZED VIEW
to read like a table. But don't manage relationships inside a single big JSON document.
the obvious way was to create tables for these many to many relations, containing user_id and document_id
is the way to go!