Suppose that Group and Member are two java objects. Each Member occurs only once in a Group, a Group has zero or more Members. The attributes of these two objects are almost the same. A Member, however, inherits its values from the Group that it belongs to. But the values of the member can be overwritten as well.
假设组和成员是两个java对象。每个成员在一个组中只出现一次，一个组有0个或多个成员。这两个对象的属性几乎相同。然而，成员从其所属的组继承其值。但是成员的值也可以被覆盖。
What is the best way to design this in a database? The only thing I can think of is to have two tables that represent these two objects. But this mean that the member table contains many similar values.
在数据库中设计这个的最佳方式是什么?我能想到的唯一的事情是有两个表来表示这两个对象。但是这意味着成员表包含许多相似的值。
Group table
id| attr1 | attr2 |
---------------------
1 | value1 | value2 |
Member table
id| attr1 | attr2 | group_id | attr3 |
----------------------------------------
1 | value1 | value2 | 1 | foo |
2 | bar | value2 | 1 | foo |
As you can see the member 1 has "inherited" its values from group 1 and has its own attr3 value foo. Member 2 also "inherited" values from group 1 but its attr1 value has been overwritten by bar.
正如您所看到的，成员1从组1“继承”了它的值，并拥有自己的attr3值foo。成员2也从第1组继承了值，但是它的attr1值被bar覆盖。
2 个解决方案
#1
2
What is the best way to design this in a database?
在数据库中设计这个的最佳方式是什么?
The best way is to understand the scientific principles, that data and program elements (including objects) are completely different species, and each has quite different methods and rules for analysis, design, and implementation. A real man and a real woman make a great marriage, precisely because each is different. A confused or enmeshed partner makes a disastrous marriage.
最好的方法是理解科学原理，即数据和程序元素(包括对象)是完全不同的物种，每个元素都有非常不同的分析、设计和实现方法和规则。一个真正的男人和一个真正的女人组成一个伟大的婚姻，正是因为他们是不同的。一个困惑或陷入困境的伴侣会造成一场灾难性的婚姻。
Therefore I will address the data requirement in your question, using standards, such that the data is stable and easy to extend. And you are free to build any object from that, either using Standards, such that the objects are stable and easy to extend. Or not.
因此，我将使用标准来解决您问题中的数据需求，以便数据稳定且易于扩展。你可以*地从中构建任何对象，或者使用标准，这样对象是稳定的，易于扩展。与否。
Here is the Normalised Relational database that supports your stated requirement.
下面是支持您所述需求的规范化关系数据库。
Group vs Member Attribute Data Model
组vs成员属性数据模型
-
No Nulls. No duplicates of anything. No Update Anomalies.
没有null。没有任何的副本。没有更新异常。
-
In the default case, the Group attributes are each Members attributes. These defaults should not be stored per Member, that would be massive and unnecessary duplication.
在默认情况下，组属性是每个成员属性。这些默认值不应该存储在每个成员中，这将是巨大的和不必要的重复。
-
You need Optional Columns for the Member attributes that, if set, override the default attributes.
您需要为成员属性设置可选的列，如果设置，则覆盖默认属性。
- I have given each attribute separately, allowing each of them to be set independently over time. If all of them were to be set together, you can merge them into one optional table.
- 我分别给出了每个属性，允许随着时间的推移分别设置它们。如果要将它们全部设置在一起，可以将它们合并到一个可选的表中。
-
Relational Keys are provided, which means you will have the highest level of Relational Integrity, power, and speed. Given the level of your question, you may not appreciate the value of that right now, but you will appreciate it once you start coding.
提供了关系键，这意味着您将拥有关系完整性、功能和速度的*别。考虑到你的问题的程度，你现在可能不理解它的价值，但是一旦你开始编码，你就会欣赏它。
-
That is an IDEF1X data model. IDEF1X is the Standard for modelling Relational Databases. Please be advised that every little tick; notch; and mark; the crows foot; the solid vs dashed lines; the square vs round corners; means something very specific and important. Refer to the IDEF1X Notation. If you do not understand the Notation, you will not be able to understand or work the model.
这是一个IDEF1X数据模型。IDEF1X是建模关系数据库的标准。请注意每一个小的滴答声;切口;和马克;乌鸦脚;实线vs虚线;正方形与圆形的角;意思是非常具体和重要的东西。参考IDEF1X表示法。如果您不理解符号，您将无法理解或操作模型。
Of course, a Member should occur just once in each Group (otherwise you would have row duplication, which is prohibited in the Relational Model).
当然，一个成员应该在每个组中只出现一次(否则会出现行复制，这在关系模型中是禁止的)。
A Member, however, inherits its values from the Group that it belongs to
但是，成员从属于它的组继承它的值。
That implies that each Member belongs to just one Group.
这意味着每个成员只属于一个组。
-
If Members can belong to more than one Group, we have to (a) specify which Group he receives default attributes from, and (b) change the model. It is easy.
如果成员可以属于多个组，我们必须(a)指定从哪个组接收默认属性，(b)更改模型。它是很容易的。
-
If you would like the Predicates, please ask.
如果你想要谓词，请问。
What is the best way to design this in a database?
在数据库中设计这个的最佳方式是什么?
That of course, leads to the next, and obvious, question: What is the best way to design the objects to use the database?
当然，这就引出了下一个显而易见的问题:设计使用数据库的对象的最佳方式是什么?
-
If I were in your position, I would use a View each, gather all the data for Group, and for Member, and then use that to load your objects. If you need the code for that, just ask.
如果我在你的位置，我会使用一个视图，收集组和成员的所有数据，然后使用它加载对象。如果您需要代码，只需询问。
-
Keep the objects simple, you do not need to mess around with trying to implement "inheritance" in the objects. That is, keep the data issues in the database, and the object issues in the objects, and do not scramble your eggs. We build software components for deployment, not pre-1970 style monolithic object layers.
保持对象简单，您不需要在对象中尝试实现“继承”。也就是说，将数据问题保存在数据库中，对象问题保存在对象中，并且不要打乱您的鸡蛋。我们为部署构建软件组件，而不是1970年以前的风格单片对象层。
-
And of course, use ACID Transactions to update the database, not OO or ORM "persistence".
当然，使用ACID事务来更新数据库，而不是OO或ORM“持久性”。
-
It is 2015, after all, and we have had the Relational Model since 1970; SQL platforms including ACID since 1984. There is no need to regress to ancient filing systems. I give this warning because I am quite aware that the OO/ORM crowd advise the implementation of pre-relational filing systems.
毕竟，现在是2015年，我们从1970年开始建立了关系模型;包括ACID在内的SQL平台。没有必要倒退到古老的档案系统。我之所以提出这一警告，是因为我非常清楚OO/ORM团队建议实现前关系归档系统。
Please feel free to ask questions or comment.
请随意提问或评论。
#2
0
You can try to have attr1 and attr2 fields in Member table contain NULL initially. And you will be able to check if attr1 or attr2 is NULL than you need to query Group table If attr1 and attr2 contain some values that means those field(s) were overwritten.
您可以尝试让成员表中的attr1和attr2字段最初包含NULL。您将能够检查attr1或attr2是否为NULL，如果attr1和attr2包含一些值，这意味着这些字段被覆盖。
#1
2
What is the best way to design this in a database?
在数据库中设计这个的最佳方式是什么?
The best way is to understand the scientific principles, that data and program elements (including objects) are completely different species, and each has quite different methods and rules for analysis, design, and implementation. A real man and a real woman make a great marriage, precisely because each is different. A confused or enmeshed partner makes a disastrous marriage.
最好的方法是理解科学原理，即数据和程序元素(包括对象)是完全不同的物种，每个元素都有非常不同的分析、设计和实现方法和规则。一个真正的男人和一个真正的女人组成一个伟大的婚姻，正是因为他们是不同的。一个困惑或陷入困境的伴侣会造成一场灾难性的婚姻。
Therefore I will address the data requirement in your question, using standards, such that the data is stable and easy to extend. And you are free to build any object from that, either using Standards, such that the objects are stable and easy to extend. Or not.
因此，我将使用标准来解决您问题中的数据需求，以便数据稳定且易于扩展。你可以*地从中构建任何对象，或者使用标准，这样对象是稳定的，易于扩展。与否。
Here is the Normalised Relational database that supports your stated requirement.
下面是支持您所述需求的规范化关系数据库。
Group vs Member Attribute Data Model
组vs成员属性数据模型
-
No Nulls. No duplicates of anything. No Update Anomalies.
没有null。没有任何的副本。没有更新异常。
-
In the default case, the Group attributes are each Members attributes. These defaults should not be stored per Member, that would be massive and unnecessary duplication.
在默认情况下，组属性是每个成员属性。这些默认值不应该存储在每个成员中，这将是巨大的和不必要的重复。
-
You need Optional Columns for the Member attributes that, if set, override the default attributes.
您需要为成员属性设置可选的列，如果设置，则覆盖默认属性。
- I have given each attribute separately, allowing each of them to be set independently over time. If all of them were to be set together, you can merge them into one optional table.
- 我分别给出了每个属性，允许随着时间的推移分别设置它们。如果要将它们全部设置在一起，可以将它们合并到一个可选的表中。
-
Relational Keys are provided, which means you will have the highest level of Relational Integrity, power, and speed. Given the level of your question, you may not appreciate the value of that right now, but you will appreciate it once you start coding.
提供了关系键，这意味着您将拥有关系完整性、功能和速度的*别。考虑到你的问题的程度，你现在可能不理解它的价值，但是一旦你开始编码，你就会欣赏它。
-
That is an IDEF1X data model. IDEF1X is the Standard for modelling Relational Databases. Please be advised that every little tick; notch; and mark; the crows foot; the solid vs dashed lines; the square vs round corners; means something very specific and important. Refer to the IDEF1X Notation. If you do not understand the Notation, you will not be able to understand or work the model.
这是一个IDEF1X数据模型。IDEF1X是建模关系数据库的标准。请注意每一个小的滴答声;切口;和马克;乌鸦脚;实线vs虚线;正方形与圆形的角;意思是非常具体和重要的东西。参考IDEF1X表示法。如果您不理解符号，您将无法理解或操作模型。
Of course, a Member should occur just once in each Group (otherwise you would have row duplication, which is prohibited in the Relational Model).
当然，一个成员应该在每个组中只出现一次(否则会出现行复制，这在关系模型中是禁止的)。
A Member, however, inherits its values from the Group that it belongs to
但是，成员从属于它的组继承它的值。
That implies that each Member belongs to just one Group.
这意味着每个成员只属于一个组。
-
If Members can belong to more than one Group, we have to (a) specify which Group he receives default attributes from, and (b) change the model. It is easy.
如果成员可以属于多个组，我们必须(a)指定从哪个组接收默认属性，(b)更改模型。它是很容易的。
-
If you would like the Predicates, please ask.
如果你想要谓词，请问。
What is the best way to design this in a database?
在数据库中设计这个的最佳方式是什么?
That of course, leads to the next, and obvious, question: What is the best way to design the objects to use the database?
当然，这就引出了下一个显而易见的问题:设计使用数据库的对象的最佳方式是什么?
-
If I were in your position, I would use a View each, gather all the data for Group, and for Member, and then use that to load your objects. If you need the code for that, just ask.
如果我在你的位置，我会使用一个视图，收集组和成员的所有数据，然后使用它加载对象。如果您需要代码，只需询问。
-
Keep the objects simple, you do not need to mess around with trying to implement "inheritance" in the objects. That is, keep the data issues in the database, and the object issues in the objects, and do not scramble your eggs. We build software components for deployment, not pre-1970 style monolithic object layers.
保持对象简单，您不需要在对象中尝试实现“继承”。也就是说，将数据问题保存在数据库中，对象问题保存在对象中，并且不要打乱您的鸡蛋。我们为部署构建软件组件，而不是1970年以前的风格单片对象层。
-
And of course, use ACID Transactions to update the database, not OO or ORM "persistence".
当然，使用ACID事务来更新数据库，而不是OO或ORM“持久性”。
-
It is 2015, after all, and we have had the Relational Model since 1970; SQL platforms including ACID since 1984. There is no need to regress to ancient filing systems. I give this warning because I am quite aware that the OO/ORM crowd advise the implementation of pre-relational filing systems.
毕竟，现在是2015年，我们从1970年开始建立了关系模型;包括ACID在内的SQL平台。没有必要倒退到古老的档案系统。我之所以提出这一警告，是因为我非常清楚OO/ORM团队建议实现前关系归档系统。
Please feel free to ask questions or comment.
请随意提问或评论。
#2
0
You can try to have attr1 and attr2 fields in Member table contain NULL initially. And you will be able to check if attr1 or attr2 is NULL than you need to query Group table If attr1 and attr2 contain some values that means those field(s) were overwritten.
您可以尝试让成员表中的attr1和attr2字段最初包含NULL。您将能够检查attr1或attr2是否为NULL，如果attr1和attr2包含一些值，这意味着这些字段被覆盖。