Updating two tables at once Postgresql

Question

Here is simplified version of my database:

+-----------+
|   USER    |           +-------------------+
+-----------+           |       EMAIL       |
|user_id    |           +-------------------+
|email_id FK+-+--------<+email_id           | 
|email_name |           |email_domain UNIQUE|
+-----------+           +-------------------+

As you can see, I have normalized email so that email domain and email name are separated
(e.g. if email = [email protected] then email_name = foo; email_domain = example.com)

But this introduced problem that I cannot solve on my own. When inserting into USER, I want to check if email_domain exists and if it does, then take that email_id and insert it as email_id in USER, else I want to create new EMAIL, get that newly created email's ID and insert it into user as email_id.
Is there some standard way when dealing with inserting into multiple tables at once ?

tomjm · Accepted Answer · 2017-10-01 12:24:31Z

1

One way of doing this is using a trigger on insert and update (and maybe delete) which will look in the email table and update the user table accordingly. This will ensure the data never gets corrupted and badly updated.

Another way is creating a stored procedure to do inserts and updates in the user table.

The third way is to do this in application code which is just like the stored procedure except not part of the database and not easily reusable by other applications.

Any of these methods are perfectly reasonable ways of doing it and have their advantages and disadvantages.

answered Oct 1, 2017 at 12:24

tomjm

3272 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Updating two tables at once Postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related