I'm using MySQL and i'm getting the next scenario:
Table nodes: node_id, lat, lng, name Table links: node1, node2, name
So you will find two tables, in table nodes, it stores all of the point as well as their particular latitude and longitude, as well as in the table link, where store node1 which reference nodes, and node2, which reference nodes too.
Since in MySQL and Rails we can not genuinely have 2 foreign key pointing towards the same table (correct me should i be wrong) and for instance if I wish to discover the beginning node_title and ending node_title, wouldso would I construct my SQL statement? I attempted
SELECT nodes.name from Nodes, links WHERE nodes.node_id = node1 which kinda works but very slow (I've under 10k records in every table), and when I wish to find both names for beginning node and ending node, how do i review and get it done? Or maybe I wish to limit the beginning node with lat > x and ending node with lat < y to locate all of the links?
yes this can be done. your table structure looks good.
your totally also good. try ensuring you've proper indexes around the node_id to assist performance.
you are able to run 2 queries, one for every title, or perform a union r two subqueries if you would like the outcomes to any or all maintain exactly the same query.