Good day to all!

Please help with optimization. I drew a DB scheme (drawn far from the rules, but the approximate essence is clear), created all the tables and columns in PostgreSQL, I start to fill in and then begin dancing with tambourines.

enter image description here

When I try to fill in a table where there are secondary keys, an error constantly occurs that says that the secondary key cannot be filled, because the reference to the primary key of the source table is empty. Roughly speaking: filling in the Equipment table, the “elephant” swears at the supplier’s secondary key ID , because in the Supplier table the primary key of the supplier ID is empty. You start to fill the Supplier table, he already swears at the secondary key ID equipment referring to the Equipment table - and so on the closed circle ...

As a result, I came to the conclusion that I have a curve implementation of the database itself at the root. Here it is. Please help understand. Thank you in advance!

2 answers 2

You have many-to-many relationships in the tables, which is evil. If such a situation arises, it is necessary to select an intermediate table that would implement two one-to-many relations.

Оборудование | Eqipment_ID | int | PK | | EqType_ID | int | FK | | Eqipment_Name | varchar(50) | | Поставщик | Producer_ID | int | PK | | Producer_Name | varchar(50) | | | Producer_Address | varchar(50) | | | Producer_Phone | varchar(50) | | | Producer_Mail | varchar(50) | | Конкретное оборудование | CurEqipment_ID | int | PK | | Equipment_ID | int | FK | -- ссылаемся на таблицу "Оборудование" | Producer_ID | int | FK | -- cсылаемся на таблицу "Поставщик" | CurEqipment_Count | int | | | CurEqipment_Price | float | | | CurEqipment_Warranty | int | |

    And why do you even need the supplier ID and order ID, and especially your computer ID?

    Your client table is fine, in terms of keys.

    You have a problem in recursive dependencies.

    Think about what is included, and not what you will request from the table, it is possible to make complex queries into the database with a selection of several tables.

    In addition, as the first responder wrote, if you have, for example, several suppliers for the same equipment and several types of equipment for the same supplier, you need a table for connections.