How to add many-to-one relationship in PostgreSQL

Question

I having two table: Stock Master and Stock In, how do i create many-to-one relationship between them, Stock In record many different stock by different date and quantity, but Stock Master must showing and combine same stock with their quantity into one , and must functional first in first out.

Answer 1

unless I'm missing something here you would handle this byu the appropriate primary/foreign key relationships

Answer 2

It doesn't sound like a many-to-one is what you really need.

If I understand correctly, you have inventory coming in at different times of different types. You want to record what has come in, you want to see how much of a specific type you have, and you want to be able to identify oldest received batch so you can prioritise that for shipping.

Vastly simplified, you'd just have that one table recording received shipments with a time and date received column which you can call WHERE clauses on to determine which entry is the oldest and should therefore be shipped.

You don't need a table as such for aggregating inventory (ignoring options like materialized views and such for now). Just sum the quantity column; group by product type.

Answer 3

If you want to create a view in Postgresql (as it appears you do from your comment to JosefAssad's advice), as in just about any other SQL db, use something like:

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