T
Your problem really is of design: you have taken into account only the entities, not the relationships that, like M:N cardinality, you cannot ignore them. I give you the ER diagram: I understand that with this track you'll see much clearer. If you need help to implement this model in MySQL, ask me in the comments and add your dataset in SQL to your question modifying and, on phpMyAdmin, Export. I'll gladly add it.ImplementationDetail the instructions that modify the dataset you provide:ALTER TABLE comidas DROP FOREIGN KEY comidas_ibfk_1;
ALTER TABLE dietas DROP FOREIGN KEY dietas_ibfk_1;
CREATE TABLE contienen(
id_contienen int AUTO_INCREMENT PRIMARY KEY,
id_dieta int,
id_comida int,
FOREIGN KEY(id_dieta) REFERENCES dietas(id_dieta),
FOREIGN KEY(id_comida) REFERENCES comidas(id_comida),
UNIQUE(id_dieta,id_comida)
);
CREATE TABLE incluyen(
id_incluyen int AUTO_INCREMENT PRIMARY KEY,
id_comida int,
id_alimento int,
FOREIGN KEY(id_comida) REFERENCES comidas(id_comida),
FOREIGN KEY(id_alimento) REFERENCES alimentos(id_alimento),
UNIQUE(id_comida,id_alimento)
);
Tables corresponding to relationships contienen e incluyen They will record which meals belong to each diet, and the same food may be present in several diets. And, similarly, for foods that will also be present in various meals. The database diagram would be as follows: Something similar will happen to you profesionales. As you modeled it, every professional would be specialized in a single diet. Which will depend on the reality you're trying to model.In addition, this opens up a lot of possibilities: now you can indicate what amount of a certain food carries every meal or how many meals of each type are indicated weekly for a diet, simply adding a field.Note that, according to Chen’s ER model https://es.wikipedia.org/wiki/Modelo_entidad-relaci%C3%B3n There must be a relación between two entidades. Another thing is that, if the cardinality is 1:N, you can propagate fields that do not admit duplicates to the boards of the entities. That is what is done, among other things, by moving from the ER to the relational model. Otherwise, what just happened to you will happen to you: there is no way to enter the data.Try this and, if you have difficulty registering any data, expand the question and notify me in the comments. Cheer up, you almost got it.Clarifications to commentsWith respect to your consultation, I propose this other way to do it. It is exactly the same but with a less generic SQL, plus MySQL:SELECT * FROM incluyen
JOIN alimentos USING(id_alimento)
JOIN comidas USING(id_comida)
WHERE comidas.id_comida=2;
On adding attributes, you have to change the way of thinking: the fields can only spread them to a table in case of a 1:N cardinality. First it has to add a relationship table. You'll see if you can delete it or not. In fact, in comidas You have a id_alimento I'd be very funny, he's screaming for a:ALTER TABLE comidas DROP id_alimento;
In the case of professionals, depending on who is 1 or N, the id will go on one board or the other. Can a diet be assigned to more than one professional? Or can a professional prescribe more than one diet?As you comment that each diet can only be prescribed by a professional, you can dispense with the table prescriben and propagate the field id_profesional to the table dietas. Getting each diet record to only admit a single data: the professional that prescribes it and only one.
