-1

I am building an API that (I am deliberately simplifying the schema below to only focus on what is questionable):

I have a table that roughly looks like this:

CREATE TABLE IF NOT EXISTS some_table
(
    id              INTEGER GENERATED ALWAYS AS IDENTITY                                   NOT NULL PRIMARY KEY,
    user_ids        TEXT[]                                                                 NOT NULL,
    field_1         TEXT                                                                   NOT NULL,
    field_2         TEXT                                                                   NOT NULL,
    field_3         TEXT                                                                   NOT NULL,
    hash_id         TEXT GENERATED ALWAYS AS ( MD5(field_1 || field_2 || field_3)) STORED UNIQUE NOT NULL
)

The API is a bit trickier than a conventional CRUD in that:

(1) Inserting into the table depends on whether md5(field_1||field_2||field_3) already exists. If it does, I need to append the value user_id to the array field user_ids. Else, insert the row.

(2) Deleting a row also depends on the state of user_ids. Actually, my current implementation makes the database handle deletions in that there is a trigger that acts on updates and deletes rows whenever cardinality(user_ids) = 0.

CREATE OR REPLACE FUNCTION delete_row() RETURNS trigger AS
$$
BEGIN
    IF tg_op = 'UPDATE' THEN
        DELETE FROM some_table WHERE id = NEW.id;
        RETURN NULL;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER some_table_delete_row
    AFTER UPDATE
    ON some_table
    FOR EACH ROW
    WHEN (CARDINALITY(NEW.user_ids) = 0)
EXECUTE PROCEDURE delete_row();

As you can see, there is no traditional deleting. What really happens is removing items from user_ids until the length of the array is 0 and then the database will autoremove the row.

  1. I think that the PUT method is the best match for how I want to effectively implement upserts.

  2. It's trickier with DELETE/PUT for decrementing user_ids. Ideally, that looks likes PATCH in that only one field is modified at a time and nothing is allowed to be deleted manually.

  3. Using an auto-generated hash_id value is convenient. That said, I am not sure whether it's the best option when I think of how deletes should work. The endpoint for that is base_url/items/{hash_id}, but in this case I will also need to calculate the hashed value in code or, as another option, just always pass the object in the request so that I can do WHERE hash_id = md5($field_1 || $field_2 || $field_3).

What do you think?

1
  • I'm not quite convinced the basic approach here is the better option; but this would be more of a frame challenge than a direct answer to your question.
    – Flater
    Aug 9, 2022 at 13:38

2 Answers 2

2

Change your table. Make one row per user and get rid of this weird hash thing.

CREATE TABLE IF NOT EXISTS better_some_table
(
    id              INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY,
    user_id         TEXT NOT NULL,
    field_1         TEXT NOT NULL,
    field_2         TEXT NOT NULL,
    field_3         TEXT NOT NULL,
    UNIQUE INDEX (field_1, field_2, field_3, user_id)
)

Now adding a user is always adding a row, and deleting a user is always deleting a row.

If you need the hash_id you can just calculate it.

If you need to know all the users with the same field_1,2,3 you can just ask for them:

SELECT user_id FROM better_some_table WHERE field_1=? AND field_2=? AND field_3=?

Don't worry about the database doing too much work. Because you have a suitable index, it will optimize this query to jump directly to the needed field_1,2,3 values and then read the user_ids sequentially.

The downside of this design is that field_1,2,3 are duplicated. Depending on what they represent this new design may be denormalized (in a bad way). If they're really big values it may also waste storage space. If either of those is a problem then you might consider a different design - one that stores field_1,2,3 in a different table with only one row per unique combination.

0

Without knowing your requirements completely it's hard to tell but my feeling is that you want something like

  • One table for the unique (determined by hash) set of fields
  • A list of users that have those fields.

So store the fields in one table and the users in the other.
Your API :

  • adds any incoming fields that it has not seen before (via the hash)
  • adds a new user IDs it finds to the users_to_some_table
  • updates an existing user ID with the hash (if it's changed).
  • If you like you can remove if some_table if it has become orphaned (no users reference it).

Pseudo-schema here:

    CREATE TABLE IF NOT EXISTS some_table
    (
        field_1   TEXT  NOT NULL,
        field_2   TEXT  NOT NULL,
        field_3   TEXT  NOT NULL,
        hash_id   TEXT GENERATED ALWAYS AS ( MD5(field_1 || field_2 || field_3)) STORED 
                       PRIMARY KEY
    )

  CREATE TABLE IF NOT EXISTS users_to_some_table
    (
        user_id  TEXT PRIMARY KEY,    
        hash_id  FORIEGN KEY REFERENCES some_table(hash_ID)
    )

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.