First of all, try to study the differences between TPH
, TPC
and TPT
, so you can choose the best option for each scenario.
-
TPH
- Table by Hieraquia - You will have a single table for all Entities.
-
TPC
- Table by Concrete - You will have a table for each concrete type.
-
TPT
- Table by Type - You will have a table for each type (abstract or concrete).
In your specific case, I advise you to make% of an abstract class to prevent it from being instantiated.
For example, consider the Servico
, Pessoa
, and PessoaFisica
entities, while it's interesting to have the ability to query all people. It is important that the user always sign a PessoaJuridica
or PessoaFisica
.
Now I'm going to post the implementation for each strategy.:
PessoaJuridica
- Table by Type
public class MyContext : DbContext
{
public MyContext()
{
}
public DbSet<Servico> Servicos { get; set; }
public DbSet<ServicoA> ServicosA { get; set; }
public DbSet<ServicoB> ServicosB { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
}
}
[Table("Servicos")]
public abstract class Servico
{
[Key]
public Guid ServicoID { get; set; }
public string Descricao { get; set; }
}
[Table("ServicosA")]
public class ServicoA : Servico
{
public decimal ValorA { get; set; }
}
[Table("ServicosB")]
public class ServicoB : Servico
{
public decimal ValorB { get; set; }
}
TPT
- Table by Hieraquia
public class MyContext : DbContext
{
public MyContext()
{
}
public DbSet<Servico> Servicos { get; set; }
public DbSet<ServicoA> ServicosA { get; set; }
public DbSet<ServicoB> ServicosB { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
}
}
[Table("Servicos")]
public abstract class Servico
{
[Key]
public Guid ServicoID { get; set; }
public string Descricao { get; set; }
}
public class ServicoA : Servico
{
public decimal ValorA { get; set; }
}
public class ServicoB : Servico
{
public decimal ValorB { get; set; }
}
Note the removal of the TPH
attribute of the [Table]
and ServicoA
entities.
ServicoB
- Table by Concrete
public class MyContext : DbContext
{
public MyContext()
{
}
public DbSet<ServicoA> ServicosA { get; set; }
public DbSet<ServicoB> ServicosB { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
}
}
public abstract class Servico
{
[Key]
public Guid ServicoID { get; set; }
public string Descricao { get; set; }
}
[Table("ServicosA")]
public class ServicoA : Servico
{
public decimal ValorA { get; set; }
}
[Table("ServicosB")]
public class ServicoB : Servico
{
public decimal ValorB { get; set; }
}
Note that the TPC
entity is no longer mapped.
Finally, I'll put the script generated by Migrations for each of the above situations:
Servico
- Table by Type
CREATE TABLE [dbo].[Servicos] (
[ServicoID] [uniqueidentifier] NOT NULL,
[Descricao] [nvarchar](max),
CONSTRAINT [PK_dbo.Servicos] PRIMARY KEY ([ServicoID])
)
CREATE TABLE [dbo].[ServicosA] (
[ServicoID] [uniqueidentifier] NOT NULL,
[ValorA] [decimal](18, 2) NOT NULL,
CONSTRAINT [PK_dbo.ServicosA] PRIMARY KEY ([ServicoID])
)
CREATE INDEX [IX_ServicoID] ON [dbo].[ServicosA]([ServicoID])
CREATE TABLE [dbo].[ServicosB] (
[ServicoID] [uniqueidentifier] NOT NULL,
[ValorB] [decimal](18, 2) NOT NULL,
CONSTRAINT [PK_dbo.ServicosB] PRIMARY KEY ([ServicoID])
)
CREATE INDEX [IX_ServicoID] ON [dbo].[ServicosB]([ServicoID])
ALTER TABLE [dbo].[ServicosA] ADD CONSTRAINT [FK_dbo.ServicosA_dbo.Servicos_ServicoID] FOREIGN KEY ([ServicoID]) REFERENCES [dbo].[Servicos] ([ServicoID])
ALTER TABLE [dbo].[ServicosB] ADD CONSTRAINT [FK_dbo.ServicosB_dbo.Servicos_ServicoID] FOREIGN KEY ([ServicoID]) REFERENCES [dbo].[Servicos] ([ServicoID])
TPT
- Table by Hieraquia
CREATE TABLE [dbo].[Servicos] (
[ServicoID] [uniqueidentifier] NOT NULL,
[Descricao] [nvarchar](max),
[ValorA] [decimal](18, 2),
[ValorB] [decimal](18, 2),
[Discriminator] [nvarchar](128) NOT NULL,
CONSTRAINT [PK_dbo.Servicos] PRIMARY KEY ([ServicoID])
)
TPH
- Table by Concrete
CREATE TABLE [dbo].[ServicosA] (
[ServicoID] [uniqueidentifier] NOT NULL,
[ValorA] [decimal](18, 2) NOT NULL,
[Descricao] [nvarchar](max),
CONSTRAINT [PK_dbo.ServicosA] PRIMARY KEY ([ServicoID])
)
CREATE TABLE [dbo].[ServicosB] (
[ServicoID] [uniqueidentifier] NOT NULL,
[ValorB] [decimal](18, 2) NOT NULL,
[Descricao] [nvarchar](max),
CONSTRAINT [PK_dbo.ServicosB] PRIMARY KEY ([ServicoID])
)
Finally, note that the TPC
strategy is more limited than the other two. For example, if you want to fetch all services, you will have to merge the two collections.
The choice between TPC
and TPH
, is a trade-off between the number of columns to be retrieved from the database (% with all%) and the cost to TPT
(in the case of TPH
, you will need JOINs between tables, and this has a cost, possibly an index will become necessary).
Although there is no silver bullet for which strategy to choose, if the bank is to be manipulated only by its solution (without direct human intervention in the Database), I would say to stay with JOINS
, since it will have a superior performance when compared to TPT
.
As for your doubt with delete, deleting an entity of Type TPH
will erase the records in the TPT
and ServicoB
tables, and finally, a record in the Servicos
table will always have a child record in the ServicosB
table or the Servicos
table, but never in both.