answered question

0
Votes

1
Answer

M$0.00

cometbus | December 24, 2008 09:42 AM | in Online Communication & Networking

How can I get Oracle Connect By features in MySQL 5?

I am sure most people have heard of the limited degrees of separation one has to become connected to another person. This is much like the myspace "in your extended network" idea, though I would like my implementation to not be broken.

If person A exists in mysql, as well as persons B through Z, and some are related or known to each other, they have a relationship. Very much a family tree. While I can see how one wold start at person A, and look for the relationship to another person, thereby drilling down to other known links...

I would like to take it one step further. I think celebrities are the best example, so I would be able to enter in myself, and ask the question: "show me a tree of how I am closest to x celebrity".

Since this is not a real project, and just a hypothetical question, assume that the links are known to be valid, and there is some proven way to key one person to another that is accurate. For example, in this case, we would use an honor system where Person A logs in, and selects all the other persons that they know.

What would be the query to find out all the relationships of person A to "x celebrity"? How expensive is this query?

I am looking to build something as a real project that will use this idea as a feature, albeit in a different way, but the end result will be the same. I get the feeling this is one, that if it is a used and popular service, the database design before it is made will be highly important.

I smell a bit more recursion in this idea that I am used to, and also feel that doing most of the work in the DB layer, and not in the application layer, is going to give me the best performance.

Does anyone have suggestions, or perhaps examples of this in working use? Maybe there is a known algo of function that gets close to this already.

Thank you

| Report Report

answers (1)

1
Vote

BEST ANSWER chosen by asker | pvera | December 24, 2008 02:35 PM

Are you trying to write a data structure and queries for a hierarchy? If yes, what you are trying to do is called a self-join. You have a column with the parent of the record, so if for example it was a table of employees, you have a column called boss with the primary key of the employee record that is the record's boss. Oracle is the only one that lets you do this cleanly, if you want to do it in MySQL or SQL Server you'll have to work around it a little bit.

Tip pvera for this answer | Permalink | Report

Comment

cometbus | December 25, 2008 12:31 AM

Yes, more or less, self join is about the only way I can think to do this. The trouble is, if you look at this like a online family tree that connects everyone to everyone, there is no way to create the key to join on.

So, for example, let's say we could rely on last names as the key to join on. As the system has more and more users, and you want to build a tree of how people are related to each other, you self join on last name, sprinkle in some recursion, and you quickly have the most inefficient set of lookups on the planet.

If I want this to scale in any way, I am going to need a much more robust solution. Myspace does this to a degree, though they mess it up by seeding every account with the same first "friend", making everyone related to everyone. But remove that person, and it gets a little better in how the relations work. I know they use a MS backed data source. I know Oracle has some nice built in tools to do this stuff.

No one has made a library or equivalent for MySQL? How about postgreSQL, I am not objectionable to using that either. I just can not afford to get locked into the Gucci of databases, being Oracle.

Thanks so much for your reply.

Tip cometbus for this comment | Report

pvera | December 25, 2008 12:49 AM

What about artificially creating a key for each individual, like a GUID? This forces them to be unique and gives you something for your self joins.

For the family tree, it would be like a business flow chart where everyone has two bosses, mom and dad. Since each individual has a unique id, that's what goes into the mom and dad fields.

Tip pvera for this comment | Report