0

A legacy database I'm working with used a pattern like

CREATE TABLE [Document](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [EntityTypeID] [int] NOT NULL,  -- Tells us if the document is for a item, person, company....
    [EntityID] [bigint] NOT NULL,   -- Is the primary key value from the item, person, company table
    [Url] [nvarchar](1024) NULL,
)

There is no FK defined in the document table since each record could be describing data related to a different parent table.

Example:

Document table

ID EntityTypeID EntityID Url
1 1 123 ...
2 1 123 ...
3 2 123 ...
4 2 123 ...
5 3 567 ...
6 3 456 ...

Person table

ID Name
123 Dave

Item table

ID Name
123 Headphones

Company Table

ID Name
567 Acme
456 Globlex

Say the EntityTypeIDs are defined as Person = 1, Item = 2, and Company = 3. Then

Document.ID 1 and 2 are Dave's documents
Document.ID 3 and 4 are documents about Headphones  
Document.ID 5 are documents for Acme
Document.ID 6 are documents for Globlex

My first thought was to create views for PersonDocument, ItemDocument and CompanyDocument and in OnModelCreating do something like

modelBuilder.Entity<PersonDocument>(entity => 
{
  ...
  entity.HasOne(d => d.Person).WithMany(p => p.PersonDocument).HasForeignKey(d => d.EntityId);
  ...
 }

That works for reading the data and populating my DTO classes but I don't know if I could update or create new Document records using that approach.

When I try to add a document I get

The entity type 'PersonDocument' is not mapped to a table, therefore the entities cannot be persisted to the database. Call ToTable

How do I handle this?

philipxy
  • 14,867
  • 6
  • 39
  • 83
Keith
  • 13
  • 1
  • 1
    I've struggled a lot with these *polymorphic associations* in the past. My final conclusion is: create a table per entity. – Gert Arnold May 11 '23 at 14:49
  • Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy May 11 '23 at 21:38
  • You can model this as a `Table-Per-Hierachy`, with a generic type, which I've written about in other answers. Let me know if you want me to dig through and find an example. – Jeremy Lakeman May 12 '23 at 01:58

2 Answers2

1

in my opinion that it is better for you to have a table documents and have tables that specify which document this is related to(PersonDocumet,CompanyDocumet,ItemDocument), so that you can have a control.

when you insert data ,step1 :insert to document then insert to related to other table

Dcoument: Id,Url

Person Dcoument:

DocumentId, PersonID

CompanyDocumet:

DocumentId, CompanyID

ItemDocument:

DocumentId, ItemID

abolfazl sadeghi
  • 2,277
  • 2
  • 12
  • 20
  • We are going the create a table per entity route. There are only about a dozen tables using these 'polymorphic associations' – Keith May 11 '23 at 19:14
0

As you are working on a legacy system, you need to ensure that existing functionality remains intact. It is better to have separate tables for each type of document, though this can be tricky to manage existing data. Another way to handle this scenario is separate methods to insert/Update related to(PersonDocument, CompanyDocument, and ItemDocument) and you need to call the methods by applying business logic.

Humaira Siddiqui
  • 9
  • 3