Inheritance X Composition - Mysql C# Entity

Question

There was a doubt of inheritance modeling in C# involving the entities Pessoa, Pessoafísica and Pessoalegal, but Gypsy Morrison Mendez helped a lot! Now I have another question in another type of relationship. As I said, a person can be physical or legal. A person can still be a customer and/or supplier. And the customer can have related services. Follow the modeling:

The models in the code stayed:

[Table("pessoa")]
public class pessoa
{

    [Key]
    public int idPessoa { get; set; }

    [Required]
    [StringLength(90)]
    public string nomeRazaoSocial { get; set; }

    public virtual fornecedor fornecedor { get; set; }

    public virtual cliente cliente { get; set; }


}

[Table("pessoafisica")]
public class pessoafisica : pessoa
{

    [StringLength(15)]
    public string cpf { get; set; }

}

[Table("pessoajuridica")]
public class pessoajuridica : pessoa
{
    [StringLength(45)]
    public string cnpj { get; set; }

}

[Table("cliente")]
public class cliente
{
    [Key, ForeignKey("pessoa")]
    public int idPessoa { get; set; }

    public virtual pessoa pessoa { get; set; }

}

[Table("fornecedor")]
public class fornecedor
{

    [Key, ForeignKey("pessoa")]
    public int idPessoa { get; set; }

    public virtual pessoa pessoa { get; set; }
}

So far everything worked well... the interaction with the bank for registration, editing and consultation is great. However, when it comes to registering the services I have a flea behind my ear. Conceptually speaking. I connected the customer service and my class stayed:

[Table("servico")]
public class servico
{
    [Key]
    public int idServico { get; set; }

    [ForeignKey("cliente")]
    public int idCliente { get; set; }

    public string descricao { get; set; }

    public virtual cliente cliente { get; set; }
}

Clearly a Personal Physique is an inherited class of Person, just as Personal is an inherited class of Person. Therefore, if I had a table for people’s addresses, in the address table I would use the key from the Person table to maintain the relationship. A person will always be OR personal OR personal.

Now there is another question... from the Supplier and Customer. A person MAY OR MAY NOT BE A SUPPLIER... MAY OR MAY NOT BE A CUSTOMER. And it may be both or it may not be either.

Although a customer or supplier ALWAYS is a person, the Service entity may only be associated with a Customer and never with a supplier (at least in this application).

Here comes the first question... for this desired concept, this modeling is correct? The keys in the tables, cardinality... this is right?

Second doubt...

I understand that in the Service table the relationship key idClient should contain the primary key of the Client table, not the primary key of the Pessoa table. THAT CONCEPT IS CORRECT?

Following the valuable help of Gypsy, I set up these classes and in the service registration controller filling out the customer list was like this:

ViewBag.idCliente = new SelectList(db.cliente
                                            .Include(c => c.pessoa)
                                            .Select (c => new
                                            {
                                                idCliente = c.idPessoa,
                                                nomeRazaoSocial = c.pessoa.nomeRazaoSocial
                                            }).ToList(), "idCliente", "nomeRazaoSocial");

The application works, but when registering in the database, the idClient field of the service table receives the idPessoa field of the person table. And it’s making me uncomfortable...

Should not the idClient field of the service table have the idClient field, which is the primary key of the Client table? If so, how should I adjust my classes to make it work?

Thank you all for your help!

Answer 1

Thanks for the force, Gypsy!

Modeling had a problem. Essentially, the client table should have as primary key and foreign key the idpessoa field:

Before, she had seen the primary key as a foreign key. Thus, the bank suffered a violation by placing the person on the table.

The classes stayed as the gypsy spoke:

[Table("final.pessoa")]
public class pessoa
{
    [Key]
    public int idpessoa { get; set; }

    [Required]
    [StringLength(45)]
    public string nome { get; set; }

    public virtual cliente cliente { get; set; }

}

[Table("final.pessoafisica")]
public class pessoafisica : pessoa
{

    [StringLength(45)]
    public string cpf { get; set; }

}

[Table("final.pessoajuridica")]
public class pessoajuridica : pessoa
{

    [StringLength(45)]
    public string cnpj { get; set; }

}

[Table("final.cliente")]
public class cliente
{
    [Key, ForeignKey("pessoa")]
    public int idpessoa { get; set; }

    [StringLength(45)]
    public string codigo { get; set; }

    public virtual ICollection<servico> servico { get; set; }

    public virtual pessoa pessoa { get; set; }
}

[Table("final.servico")]
public class servico
{
    [Key]
    public int idservico { get; set; }

    public int idpessoa { get; set; }

    [StringLength(45)]
    public string descricao { get; set; }

    public virtual cliente cliente { get; set; }
}

In summary: in a relationship of cardinality 0...1, as in the case of the client with person, use as the primary key And foreign key of the dependent table the id of the main entity.

Answer 2

Its composition is incorrect. Cliente 0 to 1 or 1 to 1 ratio Pessoa. As I said in the previous answer, the right thing would be for you to do:

[Table("cliente")]
public class cliente
{
    // Cliente não deve ter Id própria. Apenas idPessoa.
    // [Key]
    // public int idCliente {get; set;}

    [Key, ForeignKey("pessoa")]
    public int idPessoa { get; set; }

    public int idCliente_Grupo { get; set; }

    public virtual cliente_grupo cliente_grupo { get; set; }

    public virtual pessoa pessoa { get; set; }

    public virtual ICollection<servico> servico { get; set; }
}

The same goes for Fornecedor.

Just as an addendum, I wouldn’t use Linq to assemble the query because Linq’s lazy/anticipated load support is limited. I would do so:

ViewBag.idCliente = new SelectList(db.Clientes
                                     .Include(c => c.Pessoa)
                                     .Select(c => new
                                      {
                                          idCliente = c.idPessoa,
                                          nomeRazaoSocial = c.Pessoa.nomeRazaoSocial
                                      }).ToList(), "idCliente", "nomeRazaoSocial");

---

Is this modeling correct? The keys in the tables, cardinality... that’s right?

With your modifications, yes. Perfect, I’d say.

I understand that on the table Servico the relationship key idCliente must contain the primary key of the table Cliente, and not the table’s primary key Pessoa. THAT CONCEPT IS CORRECT?

Yes, but as the key to Cliente is the key to Pessoa (because Cliente is, by composition, a specialisation of Pessoa), is the same.

If you want to keep the name idCliente in modeling, no problem. It’s even better because you explain by nomenclature as Servico relates to Cliente.

The field idCliente table Servico should not have the field idCliente, which is the primary key of the table Cliente?

Not. idCliente conceptually is equivalent to idPessoa, precisely because Cliente is the specialization of Pessoa, by composition. Therefore, it is correct to assemble the SelectList with the idPessoa because the selection of data Cliente, and not of Pessoa, already checking, thus, whether Pessoa is a Cliente or not.