Have Geek Brain

How to add one-to-one relationships in PostgreSQL

I have two tables, Stock Master and Stock In, how can I create a one-to-one relationship between them? Stock In records many different stocks for different dates and quantities, but Stock Master must show and combine the same stocks with their values ​​per unit and must function like the first in the first.

+1


a source to share


3 answers


This doesn't sound like what you really want.

If I understand correctly, you have inventory coming in at different times of different types. You want to record what happened, you want to know how much of a particular type you have, and whether you want to identify an older, previously received batch so you can prioritize delivery.



In contrast, you just need one table to record the received shipments with time and date, which you can call WHERE clauses, to determine which record is the oldest and therefore should be sent.

You don't need a table per se to aggregate inventory (ignoring things like materialized views, etc.). Just sum the quantity column; by product type.

+1


a source


If I don't see something here, you would handle it by using the appropriate primary / foreign key relationship.



0


a source


If you want to create a view in Postgresql (as you can see from your comment on JosefAssad's advice) as in any other SQL-db, use something like:

CREATE VIEW Stockmaster (prodid, total)
  AS SELECT prodid, SUM(quantity)
  FROM Stockin
  GROUP BY prodid
0


a source






More articles:


All Articles