I am working on an application that will be used as an extensible framework for other applications.
我正在开发一个应用程序,它将用作其他应用程序的可扩展框架。
One of the fundamental classes is called Node, and Nodes have Content. The SQL tables look like this:
其中一个基本类称为Node,而Nodes具有Content。 SQL表如下所示:
TABLE Node ( NodeId int, .... etc )
TABLE节点(NodeId int,....等)
TABLE NodeContentRelationship ( NodeId int, ContentType string, ContentId int)
TABLE NodeContentRelationship(NodeId int,ContentType string,ContentId int)
It will be up to the developers extending the application to create their own content types.
扩展应用程序的开发人员将创建自己的内容类型。
Clearly this is bad from a relationship-database point of view as it is not possible to add a foreign key relationship to NodeContentRelationship.ContentId, even though it is a foreign key column.
显然,从关系数据库的角度来看,这是不好的,因为无法向NodeContentRelationship.ContentId添加外键关系,即使它是外键列。
However, the solution is quite simple and powerful so I am reluctant to change it.
但是,解决方案非常简单和强大,所以我不愿意改变它。
What do you think - am I in for a world of pain down the track?
你觉得怎么样 - 我是否正在追寻一个痛苦的世界?
4 个解决方案
#1
4
Why is it impossible to set NoteContentRelationship.ContentId as a foreign key? You can easily use a relational inheritance model with a table Content representing an abstract base class, and various tables AnimalContent, CarContent, etc. representing derived classes.
为什么不能将NoteContentRelationship.ContentId设置为外键?您可以轻松地将关系继承模型与表示抽象基类的表Content以及表示派生类的各种表AnimalContent,CarContent等一起使用。
#2
8
Beware the Inner Platform Effect.
注意内部平台效应。
If you're trying to build an 'extensible framework' which allows developers to store data of different 'content types' and relate them to each other in a generic fashion, you may find that others have already solved this problem.
如果您正在尝试构建一个“可扩展框架”,允许开发人员存储不同“内容类型”的数据并以通用方式将它们相互关联,您可能会发现其他人已经解决了这个问题。
#3
3
This looks to me like a variation on EAV (Entity, Attribute, Value) design.
这在我看来就像EAV(实体,属性,价值)设计的变体。
The benefits and drawbacks of EAV design have been extensively documented.
EAV设计的优点和缺点已被广泛记录。
Here is a description of EAV from a sympathetic point of view:
以下是从同情的角度描述EAV:
http://ycmi.med.yale.edu/nadkarni/Introduction%20to%20EAV%20systems.htm
And here is one from a hostile point of view:
从敌对的角度来看,这是一个:
http://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-big-design-mistakes.html
Be aware of the downside of EAV before you commit thousands of man-hours into pouring data into it. It's enormously seductive to programmers, but it can become a data manager's nightmare.
在将数据投入数千个工时之前,请注意EAV的缺点。这对程序员来说非常诱人,但它可能成为数据管理员的噩梦。
#4
0
You're in for a world of hurt down the road if you ever want to actually report on this data. You just made it much much harder to write joins and such. The lack of constraints is bad, but the extra work needed on queries is (IMHO) worse.
如果您想要实际报告此数据,那么您将面临一个受伤的世界。你刚刚写了很多连接等等。缺乏约束是不好的,但查询所需的额外工作(恕我直言)更糟。
However, if you want other developers to be able to extend the system and store data in the database but not be able to change the database schema, there might not be a choice. In that case, the answer is to minimize how much gets stored this way. You might also speed it up slightly by replacing ContentType with a ContentTypeId defined in another table.
但是,如果您希望其他开发人员能够扩展系统并在数据库中存储数据但无法更改数据库架构,则可能无法选择。在这种情况下,答案是尽量减少以这种方式存储的数量。您也可以通过将ContentType替换为另一个表中定义的ContentTypeId来稍微加快速度。
#1
4
Why is it impossible to set NoteContentRelationship.ContentId as a foreign key? You can easily use a relational inheritance model with a table Content representing an abstract base class, and various tables AnimalContent, CarContent, etc. representing derived classes.
为什么不能将NoteContentRelationship.ContentId设置为外键?您可以轻松地将关系继承模型与表示抽象基类的表Content以及表示派生类的各种表AnimalContent,CarContent等一起使用。
#2
8
Beware the Inner Platform Effect.
注意内部平台效应。
If you're trying to build an 'extensible framework' which allows developers to store data of different 'content types' and relate them to each other in a generic fashion, you may find that others have already solved this problem.
如果您正在尝试构建一个“可扩展框架”,允许开发人员存储不同“内容类型”的数据并以通用方式将它们相互关联,您可能会发现其他人已经解决了这个问题。
#3
3
This looks to me like a variation on EAV (Entity, Attribute, Value) design.
这在我看来就像EAV(实体,属性,价值)设计的变体。
The benefits and drawbacks of EAV design have been extensively documented.
EAV设计的优点和缺点已被广泛记录。
Here is a description of EAV from a sympathetic point of view:
以下是从同情的角度描述EAV:
http://ycmi.med.yale.edu/nadkarni/Introduction%20to%20EAV%20systems.htm
And here is one from a hostile point of view:
从敌对的角度来看,这是一个:
http://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-big-design-mistakes.html
Be aware of the downside of EAV before you commit thousands of man-hours into pouring data into it. It's enormously seductive to programmers, but it can become a data manager's nightmare.
在将数据投入数千个工时之前,请注意EAV的缺点。这对程序员来说非常诱人,但它可能成为数据管理员的噩梦。
#4
0
You're in for a world of hurt down the road if you ever want to actually report on this data. You just made it much much harder to write joins and such. The lack of constraints is bad, but the extra work needed on queries is (IMHO) worse.
如果您想要实际报告此数据,那么您将面临一个受伤的世界。你刚刚写了很多连接等等。缺乏约束是不好的,但查询所需的额外工作(恕我直言)更糟。
However, if you want other developers to be able to extend the system and store data in the database but not be able to change the database schema, there might not be a choice. In that case, the answer is to minimize how much gets stored this way. You might also speed it up slightly by replacing ContentType with a ContentTypeId defined in another table.
但是,如果您希望其他开发人员能够扩展系统并在数据库中存储数据但无法更改数据库架构,则可能无法选择。在这种情况下,答案是尽量减少以这种方式存储的数量。您也可以通过将ContentType替换为另一个表中定义的ContentTypeId来稍微加快速度。