SQL tables relationship

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

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.

Posted 26-Jun-10 23:21pm

R.Binu Port Blair

Updated 27-Jun-10 1:26am

Sandeep Mewara

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Sandeep Mewara · Answer 1 · 2010-06-27T01:29:00

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.

R.Binu Port Blair · Answer 2 · 2010-06-27T03:06:00

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 tables relationship

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0