There is a table with companies, each company can be a supplier of some product of another company (from the same table). We need to somehow preserve which company is the supplier, which customer. There is a situation that company A is customer B for one product, and at the same time company B is customer of company A for another product. How can such an architecture be implemented without crutches in 3NF without loss of data integrity? DBMS: Postgresql.

  • one
    The usual table of relationships, what's the catch? Nameplate: supplier, customer, product. PK on all three fields, FK on the appropriate labels. - Small

1 answer 1

Make three tables:

  • Companies
  • Products
  • Deliveries

In the third table, enter the connection type:

id_provider INTEGER, -- ΠΈΠ΄ поставщика ΠΈΠ· "Компании" id_consumer INTEGER, -- ΠΈΠ΄ потрСбитСля ΠΈΠ· "Компании" id_goods INTEGER -- ΠΈΠ΄ Ρ‚ΠΎΠ²Π°Ρ€Π° ΠΈΠ· "Π’ΠΎΠ²Π°Ρ€Ρ‹"

Redundancy - zero, sufficiency - 100%.

  • and the key to do in 3 fields? - Vlad Zhaldak
  • I would make some keys. Example: "Find all suppliers", "Find all consumers", "Find the movement of goods" ... - this is at least three keys. - Majestio
  • thanks, the question is closed - Vlad Zhaldak
  • There is a small problem (litter if it is elementary). How to display the supplier name and customer name in parallel columns? I forgot to say this initially, and this is the main condition. - Vlad Zhaldak
  • select by "Deliveries" + left join by "providers" + left join by "consumers". Surely in the table "delivery" should be the date of delivery. Then + where the delivery date beetwen date range. - Majestio