Como armazenar várias opções em uma única tabela?

Eu quero projetar um aplicativo para computação de resultado.

Primeiro, eu preciso saber como armazenar registros em um database MySQL de forma que os alunos possam ter tantos cursos presos a eles, por exemplo, o aluno A pode ter 6 assuntos ligados a ele, enquanto o aluno B pode ter 12 assuntos ligados a ele .

Nesse caso, preciso saber como eu poderia projetar uma estrutura de database que permita que um campo armazene o maior número possível de assuntos em forma de matriz .

Qualquer sugestão ou uma maneira melhor de lidar com isso será muito apreciada.

    Por favor, leia sobre os conceitos de Normalização de Dados , Indexação Geral e Restrição de Chave Estrangeira para manter os dados limpos com integridade referencial. Isso fará você ir.

    Armazenar dados em matrizes pode parecer natural para você no papel, mas para o mecanismo de database, o desempenho geralmente fica sem uso de índice. Além disso, você encontrará no dia 2 que chegar e manter seus dados será um pesadelo.

    O seguinte deve começar com um bom começo como você consertar. Junta – se também.

    create table student ( studentId int auto_increment primary key, fullName varchar(100) not null -- etc ); create table dept ( deptId int auto_increment primary key, deptName varchar(100) not null -- Economics -- etc ); create table course ( courseId int auto_increment primary key, deptId int not null, courseName varchar(100) not null, -- etc CONSTRAINT fk_crs_dept FOREIGN KEY (deptId) REFERENCES dept(deptId) ); create table SCJunction ( -- Student/Course Junction table (aka Student is taking the course) -- also holds the attendance and grade id int auto_increment primary key, studentId int not null, courseId int not null, term int not null, -- term (I am using 100 in below examples for this term) attendance int not null, -- whatever you want, 100=always there, 0=he must have been partying, grade int not null, -- just an idea -- See (Note Composite Index) at bottom concerning next two lines. unique key(studentId,courseId,term), -- no duplicates allowed for the combo (note student can re-take it next term) key (courseId,studentId), CONSTRAINT fk_sc_student FOREIGN KEY (studentId) REFERENCES student(studentId), CONSTRAINT fk_sc_courses FOREIGN KEY (courseId) REFERENCES course(courseId) ); 

    Criar dados de teste

     insert student(fullName) values ('Henry Carthage'),('Kim Billings'),('Shy Guy'); -- id's 1,2,3 insert student(fullName) values ('Shy Guy'); insert dept(deptName) values ('History'),('Math'),('English'); -- id's 1,2,3 insert course(deptId,courseName) values (1,'Early Roman Empire'),(1,'Italian Nation States'); -- id's 1 and 2 (History dept) insert course(deptId,courseName) values (2,'Calculus 1'),(2,'Linear Algebra A'); -- id's 3 and 4 (Math dept) insert course(deptId,courseName) values (3,'World of Chaucer'); -- id 5 (English dept) -- show why FK constraints are important based on data at the moment insert course(deptId,courseName) values (66,'Fly Fishing 101'); -- will generate error 1452. That dept 66 does not exist -- That error is a good error to have. Better than faulty data -- Have Kim (studentId=2) enrolled in a few courses insert SCJunction(studentId,courseId,term,attendance,grade) values (2,1,100,-1,-1); -- Early Roman Empire, term 100 (made up), unknown attendance/grade insert SCJunction(studentId,courseId,term,attendance,grade) values (2,4,100,-1,-1); -- Linear Algebra A insert SCJunction(studentId,courseId,term,attendance,grade) values (2,5,100,-1,-1); -- World of Chaucer -- Have Shy Guy (studentId=3) enrolled in one course only. He is shy insert SCJunction(studentId,courseId,term,attendance,grade) values (3,5,100,-1,-1); -- Early Roman Empire, term 100 (made up), unknow attendance/grade -- note if you run that line again, the Error 1062 Duplicate entry happens. Can't take same course more than once per term 

    Algumas perguntas simples.

    Qual curso é em qual departamento?

    mostre tudo, use aliases de tabelas (abreviações) para tornar a digitação menor, legibilidade (às vezes) melhor

     select c.courseId,c.courseName,d.deptId,d.deptName from course c join dept d on c.deptId=d.deptId order by d.deptName,c.courseName -- note the order +----------+-----------------------+--------+----------+ | courseId | courseName | deptId | deptName | +----------+-----------------------+--------+----------+ | 5 | World of Chaucer | 3 | English | | 1 | Early Roman Empire | 1 | History | | 2 | Italian Nation States | 1 | History | | 3 | Calculus 1 | 2 | Math | | 4 | Linear Algebra A | 2 | Math | +----------+-----------------------+--------+----------+ 

    Quem está fazendo o curso de World of Chaucer neste período?

    (conhecendo o curso = 5)

    Os benefícios abaixo de um dos nossos índices compostos no SCJunction. Um composto é um índice em mais de uma coluna.

     select s.StudentId,s.FullName from SCJunction j join student s on j.studentId=s.studentId where j.courseId=5 and j.term=100 +-----------+--------------+ | StudentId | FullName | +-----------+--------------+ | 2 | Kim Billings | | 3 | Shy Guy | +-----------+--------------+ 

    Kim Billings está inscrito no que este termo?

     select s.StudentId,s.FullName,c.courseId,c.courseName from SCJunction j join student s on j.studentId=s.studentId join course c on j.courseId=c.courseId where s.studentId=2 and j.term=100 order by c.courseId DESC -- descending, just for the fun of it +-----------+--------------+----------+--------------------+ | StudentId | FullName | courseId | courseName | +-----------+--------------+----------+--------------------+ | 2 | Kim Billings | 5 | World of Chaucer | | 2 | Kim Billings | 4 | Linear Algebra A | | 2 | Kim Billings | 1 | Early Roman Empire | +-----------+--------------+----------+--------------------+ 

    Kim está sobrecarregada, então solte a aula de matemática

     delete from SCJunction where studentId=2 and courseId=4 and term=100 

    execute isso acima da instrução select mostrando o que Kim está tomando:

     +-----------+--------------+----------+--------------------+ | StudentId | FullName | courseId | courseName | +-----------+--------------+----------+--------------------+ | 2 | Kim Billings | 5 | World of Chaucer | | 2 | Kim Billings | 1 | Early Roman Empire | +-----------+--------------+----------+--------------------+ 

    Ah, termo muito mais fácil. Papai não ficará feliz.

    Observe coisas como SCJunction.term. Muito pode ser escrito sobre isso, eu vou pular sobre isso no momento principalmente, além de dizer que também deve estar em um FK em algum lugar. Você pode querer que seu termo pareça mais com o SPRING2015 e não com um int.

    E no que diz respeito à id. É assim que eu faria. É uma preferência pessoal. Isso exigiria conhecer id, procurando-os. Outros podem optar por ter um curso como algo como HIST101 e não 17. Esses são altamente mais legíveis (mas mais lentos no índice (apenas). Então, faça o que for melhor para você.

    Observe o índice composto

    Um índice composto (INDEX significa KEY e vice-versa) é aquele que combina várias colunas para recuperação rápida de dados. As ordens são invertidas para os dois compostos na tabela SCJunction, de modo que, dependendo do universo de consultas que vão após seus dados, o mecanismo de db pode escolher qual índice usar para recuperação mais rápida com base na coluna mais à esquerda que você está seguindo .

    Quanto à chave única, # 1, o comentário ao lado, afirmando impor nenhuma duplicata (ou seja, dados lixo) é bastante auto-explicativo. Por exemplo, o período 1 do estudante 1 1 não pode existir duas vezes nessa tabela.

    Um conceito crucial para entender é o conceito de ordenação left-most dos nomes das colunas em um índice.

    Para consultas que studentId somente ao studentId , a chave que possui studentId listada primeiro (à left-most ) é usada. Em consultas que courseId somente o courseId , a chave que possui o courseId mais à esquerda é usada. Em consultas que seguem depois de studentId e courseId, o mecanismo de db pode decidir qual chave composta deve ser usada.

    Quando eu digo “ir atrás”, quero dizer na on clause ou where clause .

    Se alguém não tivesse essas duas chaves compostas (com a coluna 1 e 2 nelas invertidas), então nas consultas em que a coluna procurada não estivesse left-most indexada, você não se beneficiaria com o uso da chave e sofreria uma lentidão na leitura de tabelas. para retornar.

    Então, esses dois índices combinam os dois conceitos a seguir

    • Recuperação rápida de dados com base na coluna mais à esquerda ou em ambas (colunas studentId e courseId)
    • Impondo a não duplicação de dados nessa tabela com base nos valores studentId, courseId e term

    O Takeaway

    O importante é que as tabelas de junit contribuem para a recuperação rápida do índice e para o gerenciamento correto de dados versus dados delimitados por vírgula (mindset de matriz) armazenados em uma coluna, e toda a miséria de usar tal construção.

    Por completo, não em uma questão que esta é a solução geral recomendada:

    O MySQL fornece o tipo de dados JSON , que permite armazenar e recuperar objects e matrizes no formato JSON .

    Dessa forma, você pode armazenar objects e matrizes inteiros em um campo, assim como uma matriz seria:

      ['subject_1', 'subject_2', 'subject_3'] 

    Especialmente iniciantes não sabem disso, e eles reinventam a roda por outra implementação de string separada por vírgula ou usando abordagens de serialização / desserialização dependentes de idioma.

    Pelo menos o JSON é muito comumente usado e facilmente analisado como um formato de troca de dados.

    Existem casos de uso válidos para usar o armazenamento de matrizes e objects dentro de um campo MySQL, por exemplo, para otimização de velocidade ou quando você tem propriedades desconhecidas ou dinâmicas que ainda deseja salvar em um database.

    No entanto, como regra geral, se você confiar no armazenamento de objects e array no MySQL, provavelmente o design do database está quebrado.