Is the database structure correct?

Question

The main functions of the system are filling / editing / deleting database tables and searching by database.

All tables, except for status and scientist, are, as it were, reference books - they are filled separately from "Scientist".

For example, "summary_scientific_work" - stores the ID of the scientific work and the corresponding scientist.

The subtables of the Education table are broken down so as to prevent typos for later searching by database.

What can be simplified / improved by adhering to all NFs?

In order to say that it can be corrected, it is necessary to fully understand the original task and all the technological processes at which the system will be used.
For example, I don’t understand why a university is listed in education, but it doesn’t indicate the specialty for which this specialist was trained
change the notation at least for such i.imgur.com/Hz7lyyU.jpg so that the foreign keys can be seen.
in general, you can come from 4 objects - a scientist, education, scientific work, all kinds of game like teachers (nakuya?).
@ des1roer Works and studies are certainly different things.
They may be based on several studies, but the link with TC studies is probably not needed.
since they are, it means that they are needed by the CU or the customer, because it is in its subject area.
@ZhiV In general, I don’t see any crime in your model, all that is being offered at the moment in the answers makes the model worse.
The only thing - a long chain of university-department specialty strains.
Perhaps a part should be arranged in the form of a tree, who knows what depth the hierarchy will still appear there.
Without an understanding of the subject area is impossible to say more

des1roer des1roer 1.832 sixteen 46 · Answer 1 · 2016-01-14T09:16:52

education is probably logical to make one table with foreign keys on directories (faculty, city, name)

for the reference book it is not necessary to create 100,500 tables if there are 3 lines (vocational school, university, school).

it is possible to make a field for an ancestor in the work_all table (though I don’t know how right it is academically, but in real life it works with a bang)

something total has no right to life. This duplication and violation of the principles of normalization. all sum is given using views

For some reason you have thrown out the table of education, so if a person has two higher ones, then it is impossible to lay down.
Judging by the CU model, a university is understood as specific educational institutions.
So the name does not need to be taken out of it because there the values of "MSU", "MSTU", etc.
And for some reason you have transformed this name into a directory of types of educational institutions.
a person can finish the MGU twice - in the afternoon and in the evening.
and this is exactly the directory as in VK i.imgur.com/InhRLeD.jpg
But then it is not clear why faculties are separate from institutions.
Usually they say "He graduated from the faculty of beekeeping of the machine-building institute", i.e.
There is always a link between the faculty and its institute.
But for some reason, the CU believes it is enough for the task to be an institution.
just as a reference book "what happens at this institute in general" hangs

RoulanD RoulanD 116 ten · Answer 2 · 2016-01-14T08:36:26

I certainly don’t know the amount of data that will be stored in the scientist table, so my suggestion might be bad in terms of performance. But I would make the field teacher_id inside the scientist table. And naturally, I would get rid of the teacher_pupil table.

Once such a table is created, then most likely one specialist may have several teachers, and with your proposal it will be impossible to store it

Is the database structure correct?

2 answers 2

More articles: