EntityFramework 6 - Handling User-defined Attributes

Question

Happy new year to all! I've just begun data modelling an ASP.NET MVC 5 app for a client who runs a Tool Hiring business. Part of the solution involves building an admin (backend) feature through which admin users can create/edit custom attributes or Tool Metadata that are attached to each tool from a particular tool group. I am working on the notion that at runtime the application shouldn't know what the Metadata Schema will be. So I started with this: Yeah, I know ... another EAV nightmare! I know that if the data is correctly normalised, and relevant indexes are created, then it shouldn't be too bad. But honestly, I don't see any other choice. So for example:

Bosch Cordless Drill

• Tool Group: Drills
• Brand: Bosch (ListItem - prepopulated from MetaAttributeListOption table)
• Type: Cordless (listItem - prepopulated from MetaAttributeListOption table)
• Keyless Chuck: Yes (Boolean)
• Voltage: 14.4Volts (Text)
• ...

Now these Attributes will serve 3 purposes:

1. Display on Frontend as "Specifications"
2. Used for filtering Tools on Frontend
3. (Potentially) Used in Reporting to determine "Popular Brands" (for example)

So I guess I'm stuck with an RDBMS (SQL Server) for this. I know that a popular approach towards this would be to use some NoSQL solution, but to be honest, I don't have much hands-on experience with it to use it in conjunction with MSSQL. I could combine the Values tables into one table where each datatype value is in its own column, but that will leave me with a lot of nulls to contend with.

So I'm left with the following questions if you could kindly help me out with:

1. Does this model work in terms of my requirement? I'm not sure I've designed the relationship of the MetaAttributeListOption table correctly.
2. Is there an alternative to this EAV approach?
3. Assuming that my model above (or derivative thereof) is my only option, how would I implement this with Entity Framework 6? For the ASP View Pages in the admin backend, I imagine I would need some sort of HTML Helper to determine the correct Editor to render and then populate accordingly.

I would greatly appreciate any help from the StackOverflow community on this. Please let me know if you need more information, and please do not close this if you deem it off-topic as I believe that my questions are programming related. Thank you!

---

EDIT: I'm starting a bounty on this worth 200 of my own points...100 for assisting/advising me on my Questions 1 & 2, and 100 points for Question 3. Thank you

Answer 1

1. The question's model looks viable, and the relationships configured correctly, with the exception that redundant OptionLabels could be created if there are lots of duplicates. There are, however, some changes and de-normalizing compromises I would make. (See #3)

2. With your filtering and reporting requirements, and relative comfort with MSSQL I think using an RDBMS is your best bet

3. I've seen the approach shown below used in a few other developers' APIs, and it seems to be a good enough compromise that is less normalized, but makes the data model simpler and querying for values much more flexible

   • I've added MetaAttributeList to allow one list to apply to multiple MetaAttributes. In this model Booleans would be represented as a Yes/No ListOption.
   • The question's model would require that searches for values examine (one of) 3 tables, and that the applicable MetaAttribute always be known in advance
   • The question's model, by default with EF Code First, would have an issue with multiple CASCADE paths, that would require use of the FluentApi (not a huge deal, but can be inconvenient to keep track of)
   • This approach would (optionally?) require that enforcement of valid ListOption entries be handled in code rather than the database
   • Displaying different types of values would not require any additional work to render properly

   • The Admin Interface would need to check for a MetaAttribute.ListOption to determine whether to display a TextBox or ListItem (and possibly a checkbox if ListItemOptions are Yes/No)

   • You may want to add another table for ToolGroups that narrows the MetaAttributes presented to the user

Note: Since the EF method and language weren't specified, I used EF Code First and VB.Net. IMO Migrations and easier transition to EF7 are reason enough to use Code First. I like the readability of VB.Net a little better, but I'll happily change to C# if needed (or use this converter).

Imports System.ComponentModel.DataAnnotations
Namespace Models
    'I didn't bother specifying string lengths with <StringLength(#)>
    Public Class HireTool
        Public Property Id As Integer
        '... other properties

        'Navigation Properties
        Public Overridable Property HireToolMetaAttributes As ICollection(Of HireToolMetaAttribute)
    End Class
    Public Class MetaAttribute
        Public Enum MetaAttributeTypeEnum
            Text = 1
            ListItem = 2
        End Enum
        Public Property Id As Integer
        Public Property Code As String
        Public Property Label As String
        Public Property Type As MetaAttributeTypeEnum
        Public Property Required As Boolean
        Public Property Position As Integer
        'Navigation Properties
        Public Overridable Property List As MetaAttributeList
    End Class
    Public Class MetaAttributeList
        Public Property ID As Integer
        Public Property Name As String
        'Navigation Properties
        <Required>
        Public Property ListOptions As ICollection(Of MetaAttributeListOption)
    End Class
    Public Class MetaAttributeListOption
        Public Property Id As Integer
        Public Property OptionLabel As String
    End Class
    Public Class HireToolMetaAttribute
        Public Property Id As Integer
        <Schema.Index> <StringLength(1000)>
        Public Property Value As String
        <Required>
        Public Overridable Property HireTool As HireTool
        <Required>
        Public Overridable Property MetaAttribute As MetaAttribute
    End Class
End Namespace        

Edit: Here's the generated SQL:

CREATE TABLE [dbo].[MetaAttributeLists] (
    [ID]   INT            IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR (MAX) NULL,
    CONSTRAINT [PK_dbo.MetaAttributeLists] PRIMARY KEY CLUSTERED ([ID] ASC)
);
CREATE TABLE [dbo].[HireTools] (
    [Id] INT IDENTITY (1, 1) NOT NULL,
    CONSTRAINT [PK_dbo.HireTools] PRIMARY KEY CLUSTERED ([Id] ASC)
);
CREATE TABLE [dbo].[MetaAttributeListOptions] (
    [Id]                   INT            IDENTITY (1, 1) NOT NULL,
    [OptionLabel]          NVARCHAR (MAX) NULL,
    [MetaAttributeList_ID] INT            NULL,
    CONSTRAINT [PK_dbo.MetaAttributeListOptions] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_dbo.MetaAttributeListOptions_dbo.MetaAttributeLists_MetaAttributeList_ID] FOREIGN KEY ([MetaAttributeList_ID]) REFERENCES [dbo].[MetaAttributeLists] ([ID])
);
CREATE TABLE [dbo].[MetaAttributes] (
    [Id]       INT            IDENTITY (1, 1) NOT NULL,
    [Code]     NVARCHAR (MAX) NULL,
    [Label]    NVARCHAR (MAX) NULL,
    [Type]     INT            NOT NULL,
    [Required] BIT            NOT NULL,
    [Position] INT            NOT NULL,
    [List_ID]  INT            NULL,
    CONSTRAINT [PK_dbo.MetaAttributes] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_dbo.MetaAttributes_dbo.MetaAttributeLists_List_ID] FOREIGN KEY ([List_ID]) REFERENCES [dbo].[MetaAttributeLists] ([ID])
);
CREATE TABLE [dbo].[HireToolMetaAttributes] (
    [Id]               INT             IDENTITY (1, 1) NOT NULL,
    [Value]            NVARCHAR (1000) NULL,
    [HireTool_Id]      INT             NOT NULL,
    [MetaAttribute_Id] INT             NOT NULL,
    CONSTRAINT [PK_dbo.HireToolMetaAttributes] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_dbo.HireToolMetaAttributes_dbo.HireTools_HireTool_Id] FOREIGN KEY ([HireTool_Id]) REFERENCES [dbo].[HireTools] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_dbo.HireToolMetaAttributes_dbo.MetaAttributes_MetaAttribute_Id] FOREIGN KEY ([MetaAttribute_Id]) REFERENCES [dbo].[MetaAttributes] ([Id]) ON DELETE CASCADE
);
GO
CREATE NONCLUSTERED INDEX [IX_Value]
    ON [dbo].[HireToolMetaAttributes]([Value] ASC);
GO
CREATE NONCLUSTERED INDEX [IX_HireTool_Id]
    ON [dbo].[HireToolMetaAttributes]([HireTool_Id] ASC);
GO
CREATE NONCLUSTERED INDEX [IX_MetaAttribute_Id]
    ON [dbo].[HireToolMetaAttributes]([MetaAttribute_Id] ASC);
GO
CREATE NONCLUSTERED INDEX [IX_MetaAttributeList_ID]
    ON [dbo].[MetaAttributeListOptions]([MetaAttributeList_ID] ASC);
GO
CREATE NONCLUSTERED INDEX [IX_List_ID]
    ON [dbo].[MetaAttributes]([List_ID] ASC);