Would you criticize the logic of my Warehouse database for MySQL (screenshots and MySQLWrkBnch model under the cut)?

Question

Database

Model on the screenshot:

Model for those who want to feel in MySQLWorkbench:
redirect to cyberforum
Tables user and rule will be used for access control.
In general, I plan to write a client in c # vs forms.

Answer 1

Zelim Beltoev : chose the second approach, it is rational. I thought about a single table for invoices. I have two types of them - receipt and requirement-invoice. Both types of invoices have the following attributes in common:
- worker_id: who issued
the invoice - unit_id: in which warehouse the invoice was issued
- type_id: incoming/receipt invoice
- lot_id: quantity and type of goods of the goods being issued/consumed
And there are also problems in logic.
According to the worker and the unit, you can easily determine in:
- the incoming invoice, where the goods came from
- the requirement-invoice, where the goods left from
Now everything looks like this (clickable): What if you need in:
- the requirement-consignment note, WHERE did the goods go?
So far, I see a solution to the problem in the following: in the "Invoice" table, remove the ID_Unit field (it is inherited from the Worker table), add two fields ID_from, ID_where - these two new fields are linked to the Administrative unit table. It seems ok, but I haven’t done double bonds more than once, and again, some kind of collective farm, even when I tried to make it a reality, the workbench fell

Answer 2

From what catches your eye:
1. The " rule " table - what will you do when new privileges appear? For example, " write_employee ". Add new column?
It would be much more logical to split it into two tables:

rules (id, rule, description)
user_rules (user_id, rule_id)

Thus, we get rid of redundancy and normalize the database: we store only those privileges that certain users have. In addition, it becomes possible to add / remove new privileges within the application (however, you need to take into account the binding of rights to certain actions).
That is, each time interval has its own price. So in the future we will be able to find out the exact cost on any date, or build a beautiful graph of price changes.
3. shop and warehouse are similar (by the way, what is the size field ?). You can replace them with two other tables:

building (id, name, address, phone, type_id)
building_types (id, name)

That is, structure tables and types of structures (warehouse, shop, stall, something else).
4. If all warehouses and shops are located in the same city, then address can be left as is. Otherwise, you can split it into sub-tables (city, region, etc.)
5. A lot of fields in different tables are repeated (price, phone, address) - perhaps you can somehow narrow it down
. In general, you need to read about normalization