Relating distinct foreign keys in a column?

Question

Hello,

I am creating a table to register materials or services that depend on two other tables to form your cost center (Foreign key), depending on the type of registration.

If I’m going to register a material, I’m going to use the Catmatpdm table in Foreign key, if it’s a service, I’m going to use the Catserservicos table.

Currently I use two distinct Foreign key to relate the tables.

public class CatMatMaterial
{
    public int Id { get; set; }
    public string Descricao { get; set; }
    public string Codigo { get; set; }
    public bool Ativo { get; set; }

    public int CatMatPdmId { get; set; }
    public CatMatPdm CatMatPdm { get; set; }

    public ICollection<MaterialServico> MateriaisServicos { get; set; }
}

public class CatSerServico
{
    public int Id { get; set; }
    public string Descricao { get; set; }
    public string Codigo { get; set; }
    public bool Ativo { get; set; }

    public int CatSerGrupoId { get; set; }
    public CatSerGrupo CatSerGrupo { get; set; }

    public ICollection<MaterialServico> MateriaisServicos { get; set; }
}

public class MaterialServico
{
    public int Id { get; set; }
    public string Descricao { get; set; }
    public string Tipo { get; set; }
    public Boolean GerenciarEstoque { get; set; }
    public float ValorUnitario { get; set; }
    public bool Ativo { get; set; }
    (...)

    public int CatSerServicoId { get; set; }
    public CatSerServico CatSerServico { get; set; }

    public int CatMatMaterialId { get; set; }
    public CatMatMaterial CatMatMaterial { get; set; }
    (...)
}

When I enter in the database I have to check the type of registration that will be (material or service) and then choose which Foreign key will be inserted.

The fact that I make this choice and have two key Foreign for the same goal is making me uncomfortable and makes me think that there is a cleaner and easier way to build that relationship.

I’ve thought about creating a relationship-free column (Foreign key) and inserting Catmatmaterialid and Catmatservicoid into it, but I might have a problem in the future if I deleted an item from the cost center (Catmatmaterial / Catserservico) and for some misfortune was deleted without checking the relationship (I need a Deletebehavior.

I cannot imagine or find a way to have a relationship between these tables more efficiently and so I need your help to know if there is a better way to map this relationship.

I use Entity Framework Core 2.2.1 and Postgresql.

PS: Unfortunately I have to use two different cost centers for materials and services, as this is how the federal government uses...

Thank you

Answer 1

You have to have the following class organization:

The Catalogobase class is ancestral to those you use as a foreign entity. It could have the following configuration:

public class CatalogoBase {
    public int Id { get; set; }
    public string Descricao { get; set; }
    public string Codigo { get; set; }
    public bool Ativo { get; set; }

    public virtual ICollection<MaterialServico> MateriaisServicos { get; set; }
}

The classes that inherit from the class Catalogobase are the catalogs Catmatmaterial and Catserservicos,

public class CatMatMaterial : CatalogoBase {
    public int CatMatPdmId { get; set; }
    public virtual CatMatPdm CatMatPdm { get; set; }
}

public class CatSerServico : CatalogoBase {
    public int CatSerGrupoId { get; set; }
    public virtual CatSerGrupo CatSerGrupo { get; set; }
}

and the aggregator class is Materialservico,

public class MaterialServico {
    public int Id { get; set; }
    public string Descricao { get; set; }
    public string Tipo { get; set; }
    public Boolean GerenciarEstoque { get; set; }
    public float ValorUnitario { get; set; }
    public bool Ativo { get; set; }
    (...)

    [ForeignKey("Catalogo")]
    public int IdCatalogo { get; set; }

    public virtual CatalogoBase Catalogo { get; set; }
}

A likely query you might want to make is the following,

...
var materialOuServico =
    dbContext.MaterialServico.Find(57).Catalogo;

if (materialOuServico is CatMatMaterial) {
    var material = materialOuServico as CatMatMaterial;
    // Processa material.
}

if (materialOuServico is CatSerServico) {
    var servico = materialOuServico as CatSerServico;
    // Processa serviço.
}