Create a graph using mysql

Question

So , there is this interesting problem in front of me ,i have two tables one with information of user with host profiles on web and other stores profiles mentioned in the website , eg : on www.abc.com i have mentioned www.xyz.com so abc.com will be part of :

source table
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| website  | varchar(255) | YES  |     | NULL    |                |
| user_id  | varchar(25)  | YES  | MUL | NULL    |                |
| web_name | varchar(255) | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

mention table will have entries (like xyz.com mentioned above)

+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| web_link | varchar(255) | YES  |     | NULL    |                |
| user_id  | varchar(25)  | YES  | MUL | NULL    |                |
| web_name | varchar(255) | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

user_id is foreign key to these tables , now i want to generate a node based graph such that i select source.web_name and mention.webname and assign them ids such that they all be unique , eg: 0-> 1 because there can be chances of 1->0

I want to know what can be the best possible way to achieve this, should i change the schema to get it done or using python selects this can be done. I am not able to figure out giving unique ids to both source.web_name and mention.webname when they reside in different table .

Answer 1

If I have understood this correctly, you have web pages that can contain urls to other webpages and you want to model the references. You can create a table of all web pages and a table of refrences

web_pages table:

id
website
etc.

and references or "mentions" table:

source_id (refers to id in web pages table)
target_id (also refers to id in web pages table)