Normalization of tables and relationships

Question

I’m starting a project from scratch and in the middle of bank modeling, I had a problem. While building the models, I couldn’t do it the right way and I ended up getting confused.

I have student information, records and logins. Where I have several occurrences for a student and several occurrences for a login, to track which login is generating the occurrence.

But here is my problem: in my table of students I have various information such as student name, father name, mother name, parent name, contact phone, main phone, contact phone and alternative phone.

I thought about putting the parents' names on a different table, as well as the phones, to leave the tables with their respective objections, subjects. That is, organize the affairs. But in doing so, I made several mistakes in doing the scaffolding, because at first it only appeared as dropdown a phone and only the father’s name. Then missing data.

How would I do it? Would I leave all the information on one table? That is, would you delete these phone tables and parents, and leave this information in a single table, that of students? Or not, separating is right, creating these two tables? And how would I?

The models:

Pupil

 //Aqui começa a declaração normal dos atributos
    public long Id { get; set; }

    public string TipoEnsino { get; set; }

    public string Nome { get; set; }

    public string Endereco { get; set; }

    public DateTime DataNascimento { get; set; }

    public DateTime AnoLetivo { get; set; }

    public int Ano { get; set; }

    public string Turma { get; set; }

    public int NumeroChamada { get; set; }

    public string Foto { get; set; }

    public string Observacoes { get; set; }
    //Aqui termina 

    //Aqui começa os relacionamentos

        //Ocorrencias
        public ICollection<Ocorrencia> Ocorrencias { get; set; }

        //Pais
        public long PaisId { get; set; }
        public Pais Pais { get; set; }

        //Telefone
        public long TelefoneId { get; set; }
        public Telefone Telefone { get; set; }
    //Aqui termina

Parents

 //Aqui começa a declaração normal dos atributos
    public long Id { get; set; }

    public string NomePai { get; set; }

    public string NomeMae { get; set; }

    public string NomeResponsavel { get; set; }
    //Aqui termina a declaração

    //Aqui começa os relacionamentos

        //Aluno
        public long AlunoId { get; set; }
        public Aluno Aluno { get; set; }
    //Aqui termina

Telephone

//Aqui começa a declaração normal de atributos
    public long Id { get; set; }

    public string Contato { get; set; }

    public string Alternativo { get; set; }

    public string Responsavel { get; set; }
    //Aqui termina a declaração normal

    //Aqui começa os relacionamentos

        //Aluno
        public long AlunoId { get; set; }
        public Aluno Aluno { get; set; }
    //Aqui termina

Answer 1

I don’t see the need to separate the information from Student Parents, unless you want to make a table of Guardians, whose cardinality for Student is 1 for N.

In any case, I’ll assume you just want to register father and mother, then the entity Aluno gets like this:

[DisplayColumn("Nome")]
public class Aluno 
{
    [Key]
    public long AlunoId { get; set; }

    public TipoEnsino TipoEnsino { get; set; } //TipoEnsino é um Enum

    [Required]
    public string Nome { get; set; }

    public string Endereco { get; set; }

    public DateTime DataNascimento { get; set; }

    public DateTime AnoLetivo { get; set; }

    public int Ano { get; set; }

    public string Turma { get; set; }

    public int NumeroChamada { get; set; }

    public string Foto { get; set; }

    public string NomePai { get; set; }

    public string NomeMae { get; set; }

    public string NomeResponsavel { get; set; }

    [NotMapped]
    public HttpPostedFileBase ArquivoFoto { get; set; }

    public string Observacoes { get; set; }

    //Ocorrencias
    public virtual ICollection<Ocorrencia> Ocorrencias { get; set; }

    //Telefone
    public virtual ICollection<Telefone> Telefones { get; set; }
}

Phone changed to cardinality N:

[DisplayColumn("Numero")]
public class Telefone 
{
    [Key]
    public long Id { get; set; }

    public TipoTelefone TipoTelefone { get; set; }

    public string Numero { get; set; }

    //Aluno
    public long AlunoId { get; set; }
    public virtual Aluno Aluno { get; set; }
}

Answer 2

In relation to this, as the Gypsy said, unless the students do not have the relationship with parents but with guardians. If it is only parents, we know that a person can only have 1 father and 1 mother (in theory), so he could leave the information of each one together with the information of the Student. About the phones, by the ratio being from 1 to n I usually see in numerous applications the use of an external table for both phone and e-mail