CODEWITHSUNDEEP
pythondatabasepostgresql
user18238793
user18238793

Reputation:

How do I update records in one table based on records updating in another table in postgreSQL?

For my stack I am using python and postgreSQL

If there are other tools that I need, I am willing to learn them, but I think it should be possible with just these.

To clarify: I have built a database to count the stock of ingredients I buy and the meals that I cook. As an example, I want to be able to remove one count of pizza dough, tomato paste, mozzarella cheese, and Italian sausage from my ingredients tables when I update the recipe table with having cooked one pizza. Is there a way to do this?

I am willing to manually create a dictionary for every recipe and it's ingredients as well.

Upvotes: 0

Views: 43

Answers (1)

MarcinJ
MarcinJ

Reputation: 3639

So let's say you have tables like this:

recipe:
   - id
   - name

ingredient:
   - id
   - name

recipe_ingredient:
   - id 
   - recipe_id (foreign key referencing recipe(id))
   - ingredient_id (foreign key referencing ingredient(id))
   - qty_needed

pantry:
   - id
   - ingredient_id (foreign key referencing ingredient(id))
   - qty

meal:
   - id
   - recipe_id (foreign key referencing recipe(id))
   - date_made

I'm assuming you want to update the contents of your pantry table when you insert a row into meal table, although you might have something else in mind given you said "updating in another table".

Since you're using python too, I'm assuming there is some kind of a backend/frontend to this database, maybe a webapp or a desktop app or whatever. So you have two options:

  1. Use your backend code to update the contents of the pantry table when a new meal is made. Knowing your recipe and its ingredients, you just issue the correct update statements for each ingredient. In a webapp this is usually done in a controller of some sort and would run in a transaction.

  2. The other option would be to handle it in a database. For this, you would create a function and a trigger which runs the function when a new row is inserted into meal, and based on the new row contents' the function would automatically update the contents of your pantry table.

Both approaches have their pros and cons, some people do not like putting "business" logic in their database as you would with approach number 2 and would definitely tell you to go for 1. Some others prefer having everything data related handled in a database for various reasons. The decision is ultimately yours.

For reference, you might want to read on CREATE TRIGGER in PostgreSQL. Backend implementation in Python depends entirely on the framework you're using (e.g. django if it's a webapp).

A different design entirely is also possible, wherein you don't have the pantry table but instead log all of your grocery shopping and then just sum up this shopping log and sum up all the meals made to find out how many ingredients you have left, using a database view for example.

Upvotes: 1

Related Questions