我应该如何在数据库和 OOP 中建模组关系？
我有 Item 实例，我希望将其分组为 ItemGroup。但是，如果将 Item 移动到另一个项目组，出于责任原因，我想跟踪更改。例如，我需要知道 Item 是否在 10 月份位于一个 ItemGroup 中，而在 9 月份是否位于另一个 ItemGroup 中。
我应该如何在数据库中对此进行建模？我应该如何在我的课程中对此进行建模，OOP？我可以看到一些不同的解决方案，但它们在某种程度上都很复杂，所以我不知道如何实现它。
我可以使用三个表，Item ItemGroup GroupRelation 并在GroupRelation 上保留时间戳。如果更新项目信息，我需要创建一个新项目和一个新的 GroupRelation。如果项目更改组，我必须创建一个新的 GroupRelation。如果组信息发生更改，我必须创建一个新组和一个新的 GroupRelation。它很复杂，因为我必须在更改时创建多个新对象。
Item
+----+---------+-----+------+
| id | item_nr | ean | name |
+----+---------+-----+------+
ItemGroup
+----+------------+-----+
| id | group_name | vat |
+----+------------+-----+
GroupRelation
+----+---------+----------+-----------+
| id | item_id | group_id | timestamp |
+----+---------+----------+-----------+
另一种解决方案可能是只有两个类 Item 和 ItemGroup 但我需要在它们两个中都有一个时间戳，以便我知道它们何时更改。如果更新组，我必须更新属于该组的所有项目，因此这也很复杂。
Item
+----+---------+-----+------+----------+-----------+
| id | item_nr | ean | name | group_id | timestamp |
+----+---------+-----+------+----------+-----------+
ItemGroup
+----+------------+-----+-----------+
| id | group_name | vat | timestamp |
+----+------------+-----+-----------+
可能还有其他解决方案，其中一个可能是将旧版本移至另一个表。但是，当要查找当前数据和旧数据时，搜索数据就会很复杂。或者我可以有一个链接到旧版本的 prev_id 列。
有没有人有类似数据模型的经验并有任何建议？对于此类问题有最佳实践吗？
I have instances of Item that I would like to have in groups ItemGroup. But if an Item is moved to another itemgroup, I would like to keep track of the change for accountability reasons. E.g. I need to know if an Item was in one ItemGroup in October and another in September.
How should I model this in the database? How should I model this in my classes, OOP? I can see a few different solutions, but they are all complicated in some way so I don't know how to implement it.
Either I could go with three tables, Item ItemGroup GroupRelation and keep an timestamp on the GroupRelation. If the item information is updated I need to create a new item, and a new GroupRelation. If the Item changes group I have to create a new GroupRelation. And if the Group information is changed I have to create a new Group and a new GroupRelation. It is complex since I have to create multiple new objects on change.
Item
+----+---------+-----+------+
| id | item_nr | ean | name |
+----+---------+-----+------+
ItemGroup
+----+------------+-----+
| id | group_name | vat |
+----+------------+-----+
GroupRelation
+----+---------+----------+-----------+
| id | item_id | group_id | timestamp |
+----+---------+----------+-----------+
An alternative solution could be to have only two classes Item and ItemGroup but then I need to have a timestamp in both of them so I know when they are changed. If Group is updated, I have to update all Items that belongs to that group, so this is also complex.
Item
+----+---------+-----+------+----------+-----------+
| id | item_nr | ean | name | group_id | timestamp |
+----+---------+-----+------+----------+-----------+
ItemGroup
+----+------------+-----+-----------+
| id | group_name | vat | timestamp |
+----+------------+-----+-----------+
And there is probably other solutions, one is maybe to move old verions to another table. But then is it complex to search for data when both current and old data should be looked up. Or I could have a column prev_id that link to the old version.
Is there anyone that have experience of similar datamodels and has any recommendation? Is there any best practices for this kind of problem?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我会选择您的
Item、ItemGroup和GroupRelation作为良好的规范化设计，重复次数最少。您的审核需求可以使用您需要审核的每个表（例如：
ItemAudit、ItemGroupAudit）建立一个附加表来建模，其中包含您需要审核的字段和时间戳。每次可审核字段发生更改时，您都会填充审核表。这样，您就拥有了历史记录，并且历史数据不会妨碍您的日常表格。
I would go with your
Item,ItemGroupandGroupRelationas a good normalized design with minimum duplication.Your auditing requirement can be modeled with an additional table for each table you need to audit (for example:
ItemAudit,ItemGroupAudit) which holds the fields you need to audit and a timestamp. You populate the audit table every time an auditable field changes.That way, you have a historical record and don't encumber your day to day tables with historical data.
根据您的描述，我认为您的模型中隐含存在一个
GroupRelationHistory的概念。我会将其设置为自己的实体，例如Item、ItemGroup和GroupRelation。在对象世界中，我会让
Item了解其GroupRelationHistory，这意味着Item对象具有对GroupRelationHistory< 的引用/代码> 对象。本质上，GroupRelationHistory只是零个、一个或多个GroupRelation的列表。引入这个概念应该得到实际业务需求的支持。去找您的客户（或某个代表）询问历史记录本身是否是一个实体并且具有一定的商业价值。如果是，请考虑这种方法并根据业务需求进行完善。然后考虑数据库模型，这很大程度上取决于具体的细化。如果不是，那么我会将历史概念设置为纯粹的审核功能，如 Oded 建议的那样。
From your description I think there is a concept of a
GroupRelationHistorywhich exists implicitly in your model. I would make it to an own entity likeItem,ItemGroupandGroupRelation.In the object world I would make
Itemto be aware of itsGroupRelationHistory, meaning that anItemobject has a reference toGroupRelationHistoryobject. EssentiallyGroupRelationHistoryis just a list of zero, one or moreGroupRelations.Introducing this concept should be backed up by real business needs. Go to your customer (or some representative) and ask if the history is an entity of its own right and has some business value. If yes, think about this approach and refine it according to the business needs. Then think about the database model, which highly depends on the specific refinements. If no, then I would make the history concept a pure auditing feature like Oded suggested.