Relate Multiple Tables to Single General Purpose T

Question

Many times I have a general purpose entity that other entities contain a collection of. I don't want to have a new collection entity for each parent entity type that needs it but would like to re-use a single general purpose entity. For performance reasons, I also don't want to explicitly define many-to-many relationships as in this answer. The simplest example would be a collection of strings.

public class MyString
{
    public Guid Id { get; set; }
    public string Value { get; set; }
}

public class MyEntity
{
    public Guid Id { get; set; }
    public virtual List<MyString> { get; set; }
}

public class MyOtherString
{
    public Guid Id { get; set; }
    public string Value { get; set; }
}

public class MyOtherEntity
{
    public Guid Id { get; set; }
    public virtual List<MyOtherString> { get; set; }
}

I'd really like to combine MyString and MyOtherString into a single entity:

public class GeneralPurposeString
{
    public Guid Id { get; set; }
    public string Value { get; set; }
}

public class MyEntity
{
    public Guid Id { get; set; }
    public virtual List<GeneralPurposeString> { get; set; }
}

public class MyOtherEntity
{
    public Guid Id { get; set; }
    public virtual List<GeneralPurposeString> { get; set; }
}

Except now I'm going to have an additional foreign key in GeneralPurposeString for every entity that contains a collection of GeneralPurposeString.

What I would like would be a way to have an additional parent category column on the GeneralPurposeString table (but not the entity) that would specify which entity the item belongs to. I use Guid for primary keys, so the tables could look something like this:

CREATE TABLE [GeneralPurposeString]
(
    [Id] uniqueidentifier NOT NULL 
         CONSTRAINT PK_GeneralPurposeString PRIMARY KEY,
    [ParentEntityCategory] uniqueidentifier NOT NULL,
    [ParentEntityId] uniqueidentifier NOT NULL,
    [Value] nvarchar(MAX)
)

And some how in Code First to specify that MyEntity has a certain category, and that it's collection of GeneralPurposeString uses that category, and MyOtherEntity uses another category (Guid) for it's collections of GeneralPurposeString.

The key would be that GeneralPurposeString could be a collection in any other entity and that loading the parent entity and including the collection would automatically load without having to explicitly specify the category.

The purposes for all of this are

1. Allow .NET code to have GeneralPurposeString code that wasn't replicated everywhere (actual utility or business logic code). This can probably also be accomplished through inheritance and explicit mapping but that would still leave multiple tables in the database (see #2).
2. Have only one table in the database for GeneralPurposeString. This is more of a tidiness issue. Performance would possibly be better with multiple tables, but indexing on ParentEntityCategory/ParentEntityId and covering Value should be good performance for lookups.
3. Not have to explicitly code this relationship and the lookups everywhere it's needed.

Answer 1

I'm thinking if I can get over #2 and be OK with a separate table behind the scenes and implementing a derived class, that will be the simplest route to go.

So just:

public class GeneralPurposeString
{
    public Guid Id { get; set; }
    public string Value { get; set; }
}

// It's just a GeneralPurposeString with a fancy MyEntity membership pin
public class MyEntityString: GeneralPurposeString {}

public class MyEntity
{
    public Guid Id { get; set; }
    public virtual List<MyEntityString> Strings { get; set; }
}

// Cool GeneralPurposeStrings belong to MyOtherEntity
public class MyOtherEntityString: GeneralPurposeString {}

public class MyOtherEntity
{
    public Guid Id { get; set; }
    public virtual List<MyOtherEntityString> Strings { get; set; }
}

public class MyContext: DbContext
{
    public DbSet<MyEntity> MyEntities { get; set; }
    public DbSet<MyOtherEntity> MyOtherEntities { get; set; }
}

I don't have to add the derived classes to the DbContext and the tables get named with the plural of the derived class by default, so it's actually pretty straight forward.

My previous train of thought with the Parent Category would require additional coding/annotation even if EF supported it. This uses purely convention and nothing extra needed in annotations or in OnModelCreating().

I'm not seeing any harm in extra tables at this point in time. I don't see a need (currently) to have all of the data in one table for reporting, but that really depends on the type of general purpose entity, so I may need to revisit this in the future, or I may just take the many-to-many route if I do need the data in one table.

And I can still have:

public static class GeneralPurposeStringExtensions
{
    public static void SassThatHoopyFrood(this GeneralPurposeString s)
    {
        // do stuff
    }
}