Foreign key with more than 1 value

Question

I’m working on the construction of my TCC and a problem has arisen. I have a schedule table, and this table needs to store some data, among them, the activities that will be spent during the year. Because of this, I created the activities table, but then I get a problem: a schedule can have several activities, how can I store more than one activity in this schedule?? The value is undefined, a schedule can have up to 47 activities, and I would like to add them dynamically to the schedule. That is, the activities table should borrow more than one key at a time in the schedule table, how to do this??

Answer 1

What you will do is a 1:n relationship, which means a schedule will have n activities.

In practice, you need to add a foreign key to the atividades referencing the primary key of your cronograma.

Assuming your bank is already modeled, you can change the atividades to have this foreign key as follows (change the names as needed):

-- Adicionando o campo para armazenar a chave estrangeira
ALTER TABLE `atividades` ADD COLUMN `cronograma_id` INTEGER NOT NULL;

-- Configurando sua chave estrangeira
ALTER TABLE `atividades`
ADD CONSTRAINT
FOREIGN KEY(cronograma_id) -- o nome do campo que vai armazenar a chave
REFERENCES cronogramas(id); -- a referência da tabela e do campo da tabela que tem essa chave