I am scanning Wikipedia's DB and retrieving topics (page names, mostly) and their relationships. I want to use it later to create a visual map.
A topic table stores the id and the label of each topic. A relationship table has an id, a weight, and the two ids of both topics it references. There are usually two rows in the relationship table for each topic pair (dog is more related to the wolf than the wolf is to the dog, for example.)
Now, each topic has a series of several (sometimes dozens) of other close topics (synonyms with a high weight, related terms with a lower but still high weight, etc.)
For example, Caffeine is a topic, and Theanine would be very closely related to it. Same for Coffee beans.
So Caffeine is a common parent but Theanine and Coffee beans aren't aware of their relatedness. The relatedness means that the both topics have a common ancestor (parent topic).
How can I store these various topics and their relations in order to accurately and efficiently (within 1s) get n-topics and compute their relatedness?
My thoughts are:
- Store additional pairs of rows in the relationship table for each cousin and brother topics? - Or, use the application code (Python, PHP, whatever) to deduce that relatedness by climbing the tree?