关系数据建模问题 - 2 个不同的表需要相同的“子数据”
我在这里阅读了许多关于 1-1 关系、建模对象类型关系等的答案。我正在尝试做一些我只是不确定什么是正确方法的事情。
我有两个不同的表，它们都需要引用/包含多边形列表（多边形实际上表示为外循环和任意数量的内循环来表示表面上的孔）。这两个表中的每一个都是一组完全不同的属性。
这是模型的视图，只有 1 个引用多边形的表：
现在我想要第二个表，每一行也表示多边形的集合。我知道如何从面向对象的角度做到这一点，但关系视图让我对什么是正确的方法感到困惑。
一种方法是向 Polygonwitholes 表添加另一个外键，其中一个外键为空，而另一个外键已填充。如下所示：
这似乎不对。所以我考虑了一个中间表，但关系似乎更面向对象而不是关系。在这些外键上使用非此即彼的方法是否不合理？或者我可以有一个整数字段，而不向数据库添加任何约束，即它是另一个表的外键，并将其用于当时使用的任何表？从查询的角度来看，我必须检索 table1 或 table2 中一行的每个多边形中的所有点。
所以我想到的选项之一是这个，但后来我考虑如何进行查询，有些事情似乎不太正确：
我知道对于真正的数据建模者来说，这将是一个显而易见的问题！这个网站对我来说很棒，这是我的第一个问题，我希望它有意义！那么对于如何建模有什么建议吗？
（好吧，我尝试发帖，但图片没有出现。我打算找人帮我发一下）
I've read many of the answers here about 1-1 relationships, modeling object-type relationships, etc. I am trying to do something that I'm just not sure what is the right way.
I have 2 different tables that both need to reference/contain lists of polygons (the polygon is actually represented as an outer loop and any number of inner loops to represent holes in the surface). Each of the 2 tables is a completely different set of attributes.
Here is a view of the model with only 1 table referencing the polygons:
Now I want to have a second table that each row also represents a collection of of polygons. I know how to do this from an Object-oriented point of view, but the relational view has me confused as to what is the right way.
One way is to add another foreign key to the polygonwitholes table, and one is null while the other is populated. That is shown here:
This just doesn't seem right. So I thought about an intermediate table, but the relationships seem more object-oriented than relational. Is this an unreasonable way to do it, to have an either/or on those foreign keys? or I could have one field that is an integer and not add any constraint to the database that it is a foreign key to another table, and use it for whichever table is used at that time? From a query perspective, I will have to retrieve all the points in each of the polygons for a row in either table1 or table2.
So one of the options I came up with was this, but then I think about how to do queries, and something just doesn't seem right:
I know that for a true data modeler, this will be an obvious question! This site has been great for me, this is my first question, and I hope it makes sense! So are there any suggestions of how this should be modeled?
(Ok, I tried to post, but the images didn't come up. Going to try to get someone to post them for me)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
所以我和人商量后做出了决定。
我采用了第三范式，并制作了中间表，将曲面与多边形连接起来，将网格与多边形连接起来。最终解决方案有下表：
Surface（ID主键）
SensorGrid（ID主键）
Polygon（ID主键）
Point(ID主键,PolygonID外键)
Surface_Polygon(surfaceID,polygonID:复合主键)
SensorGrid_Polygon(sensorGridID, PolygonID: 复合主键)
因此，两个中间表将多边形与它们是在网格中还是在表面中联系起来。我将检查以确保额外的连接不会对性能产生太严重的影响。这是最干净的解决方案，如果将来确实需要优化性能，我会考虑为 SurfacePolygon、SurfacePoint、SensorPolygon 和 SensorPoint 制作单独的表，并摆脱中间表。
感谢您的帮助。
So I made a decision after consulting with people.
I went with the 3rd-normal form and made intermediate tables to tie the surfaces to polygons and the grids to polygons. The final solution has the following tables:
Surface (ID primary key)
SensorGrid (ID primary key)
Polygon(ID primary key)
Point(ID primary key, PolygonID foreign key)
Surface_Polygon(surfaceID, polygonID: composite primary key)
SensorGrid_Polygon(sensorGridID, polygonID: composite primary key)
So the 2 intermediate tables tie the polygons to whether they are in a grid or a surface. I will check to make sure the extra join doesn't impact performance too badly. It is the cleanest solution, and if the performance really needs to be optimized in the future, i will consider making separate tables for SurfacePolygon, SurfacePoint, SensorPolygon, and SensorPoint and get rid of the intermediate tables.
Thank you for your help.
好吧，我要尝试一下。如果这确实是一对一，并且给定的 PolygonwithHoles 不能同时是表面和网格，那么我将使用您的最后一个示例，但我会删除容器表，因为它完全多余。如果出于某种原因您想要将所有带孔的多边形作为曲面和网格，则始终可以使用 sql 联合创建该表。
Ok I am going to take a stab at this. If this is truly a 1 to 1 and that a given PolygonwithHoles cannot be both a surface and a grid then I would use your last example but I would drop the container table is it is completely redundant. That table could always be created using a sql union if for some reason you wanted to get all the polygonwithholes as surfaces and grids.