How can you decrease the amount of a field in a column in Mysql?

Question

How can you decrease the amount of a field in a column in Mysql?

Navigation

#1 by (0 votes)

2

Good, I hope you can help me, I'm doing a php and mysql system of a school about registration, what I want to do is the following: I have a student form where you put your data to register after this happens to the course form where I have the course name and time for the user to select it. (here comes my query) How can I make it so that when a user selects a guitar example course this is subtracted from the course table? example if there are 5 spaces and you select 1, then when another user comes and there are 4 seats available and the spaces are finished you can not register for that course ..

Annex my tabla cursos :

  id_curso varchar(10) NOT NULL,
  nombre_curso varchar(15) NOT NULL,
  fechafinal_curso' date NOT NULL,
  fechainicio_curso' date NOT NULL,
  'horainicio' time NOT NULL,
  'horafin' time NOT NULL,
  'AULA_id_aula' int(11) NOT NULL

and my tabla aula (which in this case I'm placing her who has the ability) I do not know if I'm working well like this ..

  id_aula' int(10) NOT NULL,
  capacidad_aula int(10) NOT NULL,
  estatus_aula varchar(20) NOT NULL

I hope you can help me. Thanks

mysql sql php

asked by Lissi29 18.07.2017 в 03:59

source

1 answer

The BEFORE INSERT trigger works only on a BEFORE UPDATE Error classic asp and oracle dates

score 0 · Accepted Answer

I do not think it's right to link the capacity of a course to the aula table, because that does not depend on the classroom, but on the course. In fact, a classroom can be used for several courses with different capacities and characteristics. Remember that our designs must correspond to reality and be as independent as possible. If you link the ability of a classroom to the guitar course, it means that the classroom will serve only for that course, which is usually not the case.

I imagine a design more or less like that.

`alumnos`

id_alumno
nombre_alumno
...otras columnas

`cursos`

id_curso
curso
id_grado
id_horario
total_admitido
...otras columnas

`alumnos_cursos`

This is an associative table to control how many and which students are enrolled in each course

id (autoincremental)
id_alumno
id_curso

`grados`

id_grado
grado
...otras columnas

`aulas`

id_aula
aula
lugar
capacidad_maxima
descripcion
...otras columnas

`cursos_aulas`

This table will serve to indicate in which classroom each course will be taught. It can happen that in a classroom with a capacity for 30 students, you teach a course with a maximum capacity of 10, for reasons of space, characteristics of the classroom, the course, etc.

The fields id_curso, id_aula would constitute a unique index so that combinations are not repeated id_curso, intra_aula:

id (autoincremental)
id_curso
id_aula

As you can see in the design, the maximum amount for a course x would be stored in the field total_admitido of table cursos . If you want to show the number of available places in any form, you could do it by subtracting the value of total_admitido - COUNT (id_curso) WHERE id_curso=? in table alumnos_cursos .

The alumnos_cursos table would actually help you to know how many enrollments there are in each course, and also in which courses each student is enrolled. It would also serve to print the schedule of each student. Or the schedule of each classroom.

It is a basic design, which you can then extend to include for example qualifications, teachers, etc, etc., etc. It's not that you have to do it like that, it's just an idea, thought of quickly:)

The design can be (and should) be further refined to make it completely independent. Suppose that for the 2017-2018 school year in the course x only 15 students are admitted, but for any reason, for the 2018-2019 year, a maximum of 25 are allowed. You would then have to create a table that relates the courses and the school years, take the column total_admitido from the table cursos and put it in that new table:

`cursos_periodosescolares`

id_curso
id_periodo
total_admitido

As you can see, when designing the database one must also think about the possible evolutions that this one may have.

I hope it serves you.