How to find relationships among values in a database

Question

This is a little difficult for me to explain but I will try my best to explain to you guys in short. Iam designing a search engine for a relational database. I want this to be independant of the structure of the database i.e it can run on any database provided. I want that when the user inserts a string such as two names 'abc xyz' ,it displays all the rows containing both the names 'abc xyz' in them and if there are no such rows then it should display rows with name 'abc' and rows with name 'xyz' and show the relationship between the two. For example 'abc' is friends with 'mno' which in turn is friends with 'jkf' which in turn is friends with 'xyz'. So i want to show this relationship as well.This is just an example of the thing i want my application to do. I want it to search all such relationships , if they exist and display them. I know this example is a bit vague compared to the complexity of the application but any ideas are appreciated.

Note: I know about neo4j and orientdb Dbms which uses graph databases but i dont wanna use them. I believe a graphical approach for finding such relationships would be required. But I wanted to ask if such a task can be done using sql only. Iam using mysql as my database. please help me out woth any queries or stored procs which are suitable for my platform. from what ive learnt information_schema can come in handy but i dont know how to use it for this purpose. Also if there are any other languages which can do this job , I would like to hear about them as well.

From my perspective. the traversal of the database from point A to point B (point A nd B are words in the string given by the user) would also help me out. All opinions are fully welcomed.

score 1 · Accepted Answer · edited May 23 '17 at 11:44

I'm not sure if i got you right, but I hope this will help you anyway. If you want to search a relation between two rows over several points it's basically a graph search problem, see also Wikipedia | Graph theory. To explain that a little...
You can visualize the relation between every row as a graph, for example the persons who know each other:

A--B
\  /  
 C

Now a more complex example:

A--B--D
\  / / 
 C--E--F

In this case A knows E for example via C but also A knows E via B and D
Also B knows F via D and E or via C and E

This works for all records stored in the same table as well as for records in any other table. To access for example all nodes with a direct relation to your search record in the same table use:

SELECT * FROM TABLE WHERE referenced_rec = 
    (SELECT rec FROM TABLE WHERE value = "abc")
     AND value = "xyz"

To access all records stored in tables directly related to this table:

select * from (
    select TABLE_NAME
    from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where
    REFERENCED_TABLE_NAME = 'your_current_table')
where your_current_table.value = "abc" 
      AND (select REFERENCED_COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME = 'your_current_table') = (select COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME = 'your_current_table')
      AND (select COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME = 'your_current_table') = "xyz"

I'm pretty sure this won't work, but for now I don't have a chance to test. However getting these SQL's right is another topic and you can maybe take a look for this Stack Overflow Question

As soon as you're able to get all the records from this or any related table you can map this to graph as shown above. You can't solve this using SQL alone as you don't know how many steps will be necessary to resolve it. You can use any shortest way algorithm for this for example a Wikipedia | Djikstra. I suppose you can find information about how to implement this with a short search.

I hope this could help you a little ;-)

Best Regards
Sverre

How to find relationships among values in a database

1 Answers1