Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Mickybee
Starting Member
29 Posts |
 Posted - 2005-05-24 : 06:39:17
|
Hi,Im building a family tree database with a list of people in one table and the relationship in another.Table 1 - PeopleID Name1 Andrew2 Ben3 Charlie4 David5 EdwardTable 2 - RelationshipsID PeopleStartID PeopleEndID Relationship Type1 1 2 Son2 2 3 Son3 3 4 Nephew4 4 5 BrotherThere are a few other bits in the tables such as relationship type and dates but ive removed them to make it easier.The problem is, Im trying to find a neat way of getting a query that will allow me (When doing a search on Andrew) to find that Andrew is related to Edward. Im a little reluctant to simply add in an extra row onto the Relationships table but have included it as the example of the thing I am looking for.ID PeopleStartID PeopleEndID Relationship Type5 1 5 Son/son/Nephew/BrotherThe people table is currently quite large (10,000) records so it would be great if you clever bods could give me some pointers as to what I should be doing. Im currently performing multiple select statements to build up the view which is very slow but it does give me the result Im looking for.Your help would be hugely appreciated.Many thanks in advanceMike  |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-05-24 : 11:31:13
|
| What if there are several relationship paths between Andrew and David?What happens if someone marries their cousin? (legal in the uk).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Mickybee
Starting Member
29 Posts |
Posted - 2005-05-24 : 11:51:39
|
| There are a number of relationship paths between people, I tried to remove some of the complex ones to keep the question as simple as possible.It gets a little confusing but sometimes people at the top of the tree are related to people at the bottom in a number of ways.The example above showed a simple route from Andrew to Edwars but there may also be another route with different people from Andrew to Edward e.g. through another marriage or marriage in the same family.Where this occurs I will also add records like (apologies for the crap example, its more realistic with larger datasets:Table 2 - RelationshipsID PeopleStartID PeopleEndID Relationship Type1 1 2 Son2 2 3 Son3 3 4 Nephew4 4 5 Brother5 1 6 Step Cousin6 6 5 NephewIn the silly example above, I will do a searh on person1 and the end result will be that they are related to person5 and Person6Person5 as Son's, Son's Nephew's BrotherPerson6 as StepCousin's Nephew p.s. it all sounds rather scary now but I assure you, when you put in real people it does work.Im trying to get back a list of all the end-relationships (person5/6) when starting at a single person1 with all the relationship names in between. It would be easier to get them back as a table but Im not too bothered.Thanks for your help in advanceMike |
|
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2005-05-25 : 11:13:44
|
| I see no problem with having a Person table and a Person_Relationships table that holds the PersonID, and the RelatedToPersonID and the RelationshipType. It sounds like you are setting this up just like the real world would have it. Put indexes on both "PersonID" columns, and on the relationship type. That will speed up searching. And don't worry about the size of the table. My Person_Relationships table has over 38,000 records in it and our stored procedures that reference it are still lightning fast.Aj |
|
|
|
|
|
|
|
|
Efficient Search Query needed - Please Help