Click here to Skip to main content
15,843,031 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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.

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.
Updated 27-Jun-10 2:26am

R.Binu Port Blair wrote:
In the above example if I query for a Contact Detail of a Node all Houses are selected.

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.
Share this answer
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?
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900