0

I have multiple postgres functions that takes in a number of arguments in order to write on multiple tables.

fn_trade
(
  _product_id BIGINT[],
  _user_id BIGINT[],
  _location_id BIGINT
)

Since the arguments comes from the client or a Node.js server, I need to validate that the data exists (handled by FK constraints, but not in the case of BIGINT[]), and I need confirm that the arguments' relationships are valid. For example, I have to check that the user and product exists for the location, and the product belongs to the user. The complexity of this check grows with more arguments. I may end up with more than 10 arguments, and checking their relationships will be complicated and costly.

I don't know if I am missing a crucial picture by trying to validate function arguments. Is this the right approach? Is there a better way to have confidence in the argument's relationships so that the populated data will always be expected?

A possible approach might be to predict the result and check if the result is written once the write is done (else rollback)?

Postgres 9.6+

Edit: Added an example to clarify the question

Tables:

location (id, name) -- e.g. 1, 'Canada'

user (id, name, location_id) -- e.g. 1, Jane, 1

product (id, name, user_id, location_id) -- e.g. 1, 'skates', 1, 1

trade (id, user_id[], product_id[]) -- e.g. 1, [1,2], [1,2,3,4]

When a trade happens I need to confirm that the data given is matches the data within the database.

For example, I get:

fn_trade
(
  [1,2,3,4],
  [1,2],
  1
)

So the trade happens for users [1,2] for products [1,2,3,4] in Canada. Users can have items in different locations and users can move to different locations.

When inserting user_id[] and product_id[] into trade, the check for individual ids are not automatic. I don't know if its possible to set the values within an array as FKs, so the check here is manual. I need to guarantee ids [1,2,3,4] all exists for the product.

But what about relations? I need to check that the ids also belongs to the users [1,2].

Belonging to the user is not enough. The products should exist in the same location where the trade is happening.

And for the users, I need to know if its possible for them to trade. If they live in separate locations (Canada v.s. Taiwan), then the trade cannot happen.

Since client data relies on the DB, and data passed to fn_trade might be unreliable, how should the relation be validated to prove that the data is true for our user, products, location relations?

Dan
  • 2,455
  • 3
  • 19
  • 53
  • Foreign keys were invented just so that you do not have to manually check these relationships. If foreign keys are not correct when inserting or updating rows, the server will raise an exception. – klin Apr 27 '18 at 20:39
  • @klin I thought FK checks only as far as that the key exists in the table, not that a `product` belongs to `user` and they both live in `location`. Couldn't faulty argument be passed that contains a valid FK? I insert or update a `product` not belong to a `user` and in the wrong `location`. All the ids exists for the tables, just not for the correct relations. – Dan Apr 27 '18 at 20:52
  • I read the question and your comment again and I'm not sure I've understood the logic. It seems that you should try to deliver [a Minimal, Complete, and Verifiable example.](https://stackoverflow.com/help/mcve) – klin Apr 27 '18 at 21:21
  • @klin I added an example that will hopefully remove some ambiguity from my question. Looking at the problem, it's possible that storing arrays might be undesirable. I still lack the experience to really say. – Dan Apr 29 '18 at 23:37
  • 1
    Your database model is not properly modeled to do the FK checks as mentioned by @klin The proper way would have one row for each user/product in trade table. There is no foreign key check for array values. Here a better explanation: https://stackoverflow.com/questions/41054507/postgresql-array-of-elements-that-each-are-a-foreign-key – Jorge Campos Apr 29 '18 at 23:44
  • If it was properly modeled you wouldn't even need the function. – Jorge Campos Apr 29 '18 at 23:45
  • @JorgeCampos I might be able to refactor the schema in the future, but the suggested constraint checks in the link for the arrays is immediately helpful. Normalizing the schema should simplify the validation, but it still cannot guarantee that the passed values are related. Assuming I normalized products, I can still pass in a product that belong to someone else. The more arguments I have, the bigger the relation check. – Dan Apr 30 '18 at 02:55
  • No, if you have a constraint between an user and a product you create a table to normalize that constraint and your trade would have a FK to this table. That is all about normalization. Again, If you do it right you don't need a function to validate it. – Jorge Campos Apr 30 '18 at 11:48

0 Answers0