Hello. Recently started learning MySQL. And then he ran into questions that were not quite simple in my opinion. For example, I have a task to create a database "uni". There is a table:

  • students (id, name, patronymic, surname, faculty, course, phone_number, m_id (mothers.id), f_id (fathers.id), gr_id (groups.id));
  • groups (id, name);
  • mothers (id, name, patronymic, surname, job, phone_number);
  • fathers (id, name, patronymic, surname, job, phone_number);
  • teachers (id, name, surname);
  • disciplines (id, name);
  • marks (id, st_id (students.id), d_id (disciplines.id), t_id (teachers.id), mark);

Firstly, if, for example, about the student's parents there is information about the mother, and the parents are divorced, then f_id will be NULL? Generally, if you set the value of the m_id or f_id field, which one is better to set. because if INT NOT NULL, does the field go blank if there is no information about one of the parents?

What storage system is better for choosing for tables. After all, if I understood it would be more expedient to choose InnoDB, maybe it will give way to the speed of working with SELECT, but I can use ON ONDDATE CASCADE, ON DELETE CASCADE for the tables? Here a new question arises, and what is the difference between CASCADE and RESTRICT (CASCADE updates the values ​​in the child tables when changing or deleting records in the parent tables, and RESTRICT - when measuring or deleting the gender, changes nothing in the child if the values ​​are gender. already used in the child. Meaning to use RESTRICT when CASCADE automatically updates or deletes the entries in the generic and child tables)?

It is not clear what indexes are and whether they are necessary for me in this database. As I understand it, indexes group records by specific values ​​in different disk clusters and speed up the search, but these are not foreign keys? Do I understand correctly that foreign keys link tables, and indexes group records in tables ?????

Whether from the point of view of specialists in the field of database administration is correct, this structure and answer, please, whether I should use indices and in general what should be done. Read information from different sites. Please do not write that the vehicle is a fool, vote against it and say that the issues should be divided into separate ones. I believe that they are all connected and it does not make sense to break them. Thank you in advance.

    1 answer 1

    1. A person may be missing one and both parents. Therefore, a reference to parents cannot have the NOT NULL flag.
    2. InnoDB if there are no other requirements (for example, you might need MEMORY). If you are not sure which engine to use, use InnoDB
    3. RESTRICT / CASCADE / SET NULL Consider your example with ratings. When a student is removed (kicked out), his grades are of no interest to anyone, so they can be deleted in cascade (CASCADE). You cannot delete an item if a student has a grade on it (RESTRICT). When removing the teacher (quit) we can reset the link to it while the assessment itself will remain (SET NULL)
    4. Indices. Imagine that you have two bookcases in front of you. In one, the books are sorted alphabetically (indexed), and in the other stand as haphazardly. In which cabinet will you find the book faster? When creating an external and primary key, indexes on these fields are created automatically. The index is created on a specific field. And one field in the table can be indexed, and the second is not. Accordingly, there will be a different query execution where in the query condition or sorting there is one field and another
    5. On the structure of the tables, I have one comment: mothers and fathers tables are no different, then they need to be merged into one parents table
    • Interests three questions: 1) explain, if you are not hampered by 2 points; 2) foreign keys are modified in the tables where they are indicated, then - there are st_id.marks (CASCADE), d_id (RESTRICT), t_id (SETB NULL) and this is applicable only for FK, for PK - impossible? 3) the records in the tables are already indexed, for example, a student has 5 marks, 2 students - 3, and their order by st_id: 1 2 2 1 1 1 2 1, indexing: 1 1 1 1 2 2 2 2? - Muscled Boy
    • one
      @MuscledBoy 1) added in response. 2) How do you present actions with the RK? The action takes place when the item being referenced changes. Which element does RK refer to? 3) Forget about the "order of records in the table." There is no such thing. A little bit 4 point - Anton Shchyrov
    • Thank you for the informative and clear answer. More like that - Muscled Boy