SQL表关系 [英] SQL tables relationship
问题描述
朋友，
谁能建议我如何在2个表之间创建关系?
条件是:
1.有节点，一个或多个房屋可以连接到一个节点.
2.安装该节点的节点必须有一个父屋".
示例:
节点表包含NodeID(PK)，NodeName，SignalQuality列
房屋表包含房屋ID，NodeID(FK)，房屋名称，房屋所有者名称，电话等.
一个节点必须有其唯一的父代房屋，我们可以在整个父房屋中获得该节点的联系方式.在上面的示例中，如果我查询节点的联系方式，则所有房屋均已选中.
Hi Friends,
Can anyone suggest me how to create a relation between 2 tables?
The conditions are:
1. there are Nodes and one or more Houses can be connected to a Node.
2. there must be one Parent House for a Node on which the Node is installed.
Example:
Node Table contains NodeID(PK), NodeName, SignalQuality columns
House Table contains HouseID, NodeID(FK), HouseName, HouseOwnerName, Phone etc.
One Node must have its single Parent House throughout which we can get the Contact Details of that Node. In the above example if I query for a Contact Detail of a Node all Houses are selected.
推荐答案
在上面的示例中，如果我查询节点的联系方式，则所有房屋均已选中.
In the above example if I query for a Contact Detail of a Node all Houses are selected.
根据以上示例，如果提供一个NodeID，则可以将所有房屋重新分配到该特定房屋.看起来好像不是这里的问题.
但是，如果您希望仅获取nodeID的父小时"详细信息，(尽管系统允许将Node 1作为父节点，而将其他节点(如果有的话)作为辅助节点，则有多个房屋)，在这种情况下:
2个选项:
1.在节点表中有一个新字段，名称为"ParentHouseID(FK)"，该字段可以为空.当您在UI中映射时，仅用父HouseID填充它.
2.在房屋表中有一个新的位字段"IsParentHouseID"，如果它被映射为特定节点的父房屋ID，则将标记为true.对于映射到该节点的其余houseid，将其填充为false.
现在，在查询时，使用添加的新字段来获取数据.
Based on the above example, if you give a NodeID, you can get all the houses realted to that particular one. Doesn''t look like an issue here.
Though, if you want that only Parent Hourse details are fetched for a nodeID, (though system allows multiple houses for a Node 1 being parent and others if any being as secondary), in that case:
2 options:
1. Have a new field in Node table as ''ParentHouseID(FK)'' which can be nullable. Fill this only with parent HouseID when you map it in UI.
2. Have a new bit field in House Table as ''IsParentHouseID'' which will be marked true if it is mapped as a parent houseID for a particular node. For rest of the houseid''s mapped to that node will fill this as false.
Now while quering, use the added new field to get the data.
Sandeep，感谢您的努力.我已经尝试了您提供的第一个选项，但是通过这种方法，我遇到了问题在数据库更新过程中，但是在ADO.Net级别中正常工作.我也将这个问题作为一个问题发布-在我之前的问题中关于多重相关表"，但没有令人满意的答案.在第二个选项中，有可能添加一个以上的房屋作为节点的父级.还有其他解决方法，例如使用关键约束并添加另一个NodeHouseDetail表?
Sandeep, thanks for your effort.I had already tried the first option given by you but through that method I face a problem in database updating process but was working correctly in ADO.Net level. I posted that issue also as a question - Re Multi Related Tables in my previous question but no answers was satisfactory. In the second option there is a chance for adding more than 1 house as the parent of a node. Is there any other way to solve this like using key constraints and adding another NodeHouseDetail table?
这篇关于SQL表关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
