Here is a trick I seem to have learn here! I wonder if this could be modeled with JSON. A nice property of JSON object is that keys are an unordered set, which fits our data modelisation here:
```
CREATE TABLE mutual_friendship (
friendship JSONB NOT NULL PRIMARY KEY
);
INSERT INTO mutual_friendship(friendship) VALUES ('{"3": {}, "5":{}}');
INSERT INTO mutual_friendship(friendship) VALUES ('{"4": {}, "3":{}}'); -- FAIL!
SELECT * FROM mutual_friendship WHERE friendship ? '3';
SELECT * FROM mutual_friendship WHERE friendship ?& '{"3", "5"}';
```
* Establishing friendship: Single request, no pre-processing
* Deleting friendship: Single request, no pre-processing
* Getting the list of friends: Single query with `?` operator;
* Are they friends?: Single query with `?&`;
* Storage requirements: Not sure how much space would be required for these simple JSON object. Extra GIN index required.
* Potential invariant violations: The id in the object cannot be foreign keys.
This could be resolved by having two extra column to act as foreign key container (And only used as such, this way the what column store what id doesn't matter) and a CHECK for `(user1_id, user2_id) = jsonb_object_keys(friendship)`
Here is a trick I seem to have learn here! I wonder if this could be modeled with JSON. A nice property of JSON object is that keys are an unordered set, which fits our data modelisation here:
```
CREATE TABLE mutual_friendship (
friendship JSONB NOT NULL PRIMARY KEY
);
INSERT INTO mutual_friendship(friendship) VALUES ('{"3": {}, "5":{}}');
INSERT INTO mutual_friendship(friendship) VALUES ('{"4": {}, "3":{}}'); -- FAIL!
SELECT * FROM mutual_friendship WHERE friendship ? '3';
SELECT * FROM mutual_friendship WHERE friendship ?& '{"3", "5"}';
```
* Establishing friendship: Single request, no pre-processing
* Deleting friendship: Single request, no pre-processing
* Getting the list of friends: Single query with `?` operator;
* Are they friends?: Single query with `?&`;
* Storage requirements: Not sure how much space would be required for these simple JSON object. Extra GIN index required.
* Potential invariant violations: The id in the object cannot be foreign keys.
This could be resolved by having two extra column to act as foreign key container (And only used as such, this way the what column store what id doesn't matter) and a CHECK for `(user1_id, user2_id) = jsonb_object_keys(friendship)`