祝大家新年快乐!我刚刚开始为运行工具招聘业务的客户进行ASP.NET MVC 5应用程序数据建模。该解决方案的一部分涉及构建管理员(后端)功能,管理员用户可以通过该功能创建/编辑自定义属性或从特定工具组附加到每个工具的工具元数据。我正在研究一种概念,即应用程序在运行时不应该知道元数据模式是什么。所以我从这里开始:
是的,我知道... 另一个EAV噩梦!我知道,如果数据正确归一化并且创建了相关索引,那么它应该不会太差。但老实说,我没有其他选择。因此,例如:
博世无绳电钻
MetaAttributeListOption
表中预填充)MetaAttributeListOption
表中预填充)现在,这些属性将用于3个目的:
因此,我想为此使用RDBMS(SQL Server)。我知道一种流行的方法是使用一些NoSQL解决方案,但是老实说,我没有太多的实践经验可以将它与MSSQL结合使用。我可以将
Values
表组合到一个表中,其中每个数据类型值都在其自己的列中,但是这将给我留下很多与之抗衡的空值。因此,如果您能帮助我,我会遇到以下问题:
MetaAttributeListOption
表的关系。 我将非常感谢StackOverflow社区对此提供的任何帮助。如果您需要更多信息,请告诉我,如果您认为它离题,请不要关闭此信息,因为我认为我的问题与编程有关。谢谢!
编辑:
我现在开始以此奖励自己的200积分... 100在问题1和2上为我提供帮助/建议,在问题3上获得100积分。谢谢
最佳答案
注意:由于未指定EF方法和语言,因此我使用EF Code First和VB.Net。 IMO迁移和向EF7的更轻松过渡是使用Code First的充分理由。我更喜欢VB.Net的可读性,但是如果需要的话,我会很乐意更改为C#(或使用this转换器)。
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
编辑:这是生成的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);