Inheritance in Database

Asked

Viewed 332 times

2

I am developing an application, where I have 3 tables that have the same attributes, but as they had attributes in these tables that were mandatory for some, and not for others, I decided to separate in 3 different tables. However, I am having some problems to develop some functions that are common to the 3 tables.

What would be the best way to turn these 3 tables into one? Remembering that I have different data validation models for each table, so I guess I couldn’t just disappear with the other two.

I’m using Asp.net mvc 4 and Entity framework with database first.

Table Usuario

    [IdUsuario] int IDENTITY(1,1) NOT NULL,
    [NomeCompleto] varchar(100)  NOT NULL,
    [Email] varchar(100)  NOT NULL,
    [Senha] varchar(45)  NOT NULL,
    [Sexo] char(1)  NULL,
    [Telefone] varchar(45)  NULL,
    [About] varchar(500)  NULL,
    [Nickname] varchar(45)  NOT NULL,
    [Email2] varchar(100)  NOT NULL,
    [Endereco] varchar(100)  NULL,
    [Bairro] varchar(45)  NULL,
    [Estado] varchar(45)  NULL,
    [Cep] varchar(11)  NULL,
    [Celular] varchar(45)  NULL,
    [FotoPerfil] varchar(500)  NULL,
    [StatusPerfil] varchar(100)  NULL,
    [NBans] int  NULL,
    [Banido] bit  NULL,
    [DtCadastro] datetime  NOT NULL,
    [DtNasc] datetime  NOT NULL

Table Banca 

    [IdBanca] int IDENTITY(1,1) NOT NULL,
    [NomeCompleto] varchar(100)  NOT NULL,
    [Cpf] char(11)  NOT NULL,
    [Email] varchar(100)  NOT NULL,
    [Rg] char(8)  NOT NULL,
    [Senha] varchar(45)  NOT NULL,
    [Sexo] varchar(45)  NOT NULL,
    [DtNasc] datetime  NULL,
    [Telefone] varchar(45)  NULL,
    [About] varchar(500)  NULL,
    [Nickname] varchar(45)  NOT NULL,
    [Email2] varchar(100)  NULL,
    [Endereco] varchar(100)  NOT NULL,
    [Bairro] varchar(45)  NOT NULL,
    [Estado] varchar(45)  NOT NULL,
    [Cep] varchar(45)  NOT NULL,
    [Celular] varchar(45)  NOT NULL,
    [Foto] varchar(500)  NULL,
    [FormAcademica] varchar(45)  NOT NULL,
    [DtCadastro] datetime  NOT NULL

Table Desginer
    [IdDesigner] int IDENTITY(1,1) NOT NULL,
    [NomeCompleto] varchar(100)  NOT NULL,
    [Cpf] char(11)  NOT NULL,
    [Email] varchar(100)  NOT NULL,
    [Rg] char(8)  NOT NULL,
    [Senha] varchar(45)  NOT NULL,
    [Sexo] varchar(45)  NOT NULL,
    [DtNasc] datetime  NULL,
    [Telefone] varchar(45)  NULL,
    [About] varchar(500)  NULL,
    [Nickname] varchar(45)  NULL,
    [Email2] varchar(100)  NULL,
    [Endereco] varchar(100)  NOT NULL,
    [Bairro] varchar(45)  NOT NULL,
    [Estado] varchar(45)  NOT NULL,
    [Cep] varchar(45)  NOT NULL,
    [Celular] varchar(45)  NULL,
    [Foto] varchar(500)  NULL,
    [DtCadastro] datetime  NULL
c# asp.net-mvc database entity-framework inheritance
  • 1

    You can give an example of what your tables look like today by editing your question?

  • 1

    You reported that you are ".... having some problems to develop some functions..." but did not explain what problem you need to solve, what methods you want to create to solve a problem of your business, nor how the tables are. Tip: think about your business first, what will happen with table modeling is a consequence.

  • Just to specify, the problem you’re trying to solve has to do with something called data normalization. P.S.: I am not the same Renan from the previous comment.

  • In fact, the "problem to develop some functions" is that in certain methods that are common the three tables, I have to make queries and ifs and elses, which ends up leaving the code a little extensive, what bothers me a little... I will edit the question with my tables.

  • @Gypsy omorrisonmendez Edited.

  • Is there a problem if I disappear with the other two tables and leave only the user table?

  • Design and Banking is a type of User? If positive you can use a table only with the data in common. So you would create an extra field in this table (Typousuario) for example.

  • Yes. They are users who have functions according to their roles. For example, Banking is responsible for monitoring the site, they may delete posts, write articles, etc.

Show 3 more comments

1 answer

1


From what I understand Design and Banking are types of User.

You can use a table only and create an extra field in this table (Typousuario for example), to identify the user type.

This field can even facilitate the implementation of your business rules related to users, for example:

var usuario = _repositorio.ObterUsuarioPorId(id);

if(usuario.TipoUsuario == EnumTipoUsuario.Design)
{
    //regra para usuário tipo Design
}

if(usuario.TipoUsuario == EnumTipoUsuario.Banca)
{
    //regra para usuário tipo Banca
}

...Remembering that I have different data validation models for each table, so I guess I couldn’t just disappear with the other two...

In fact, by analyzing the information of the question, I believe you have different data validations for each user object type. If you are a user of type Design validated this/ I do such logic, if you are a user of type Banca need to validate that. But the table is the same, we are talking user.

  • If the tables were not separated, it was possible to use the inheritance in code even, but for this case your answer works perfectly.

Browser other questions tagged c# asp.net-mvc database entity-framework inheritance

You are not signed in. Login or sign up in order to post.