Hello folks, here is a pretty original (I think) problem for your minds! :-)
I'd like to store a graph in a MySQL database. By graph, I mean the graph theory meaning (i.e., "a set of connections between pairs of nodes, which (the connections) may have a direction and/or weight"), not the meaning "plot of a value over time" or "graphical representation of a function value's dependence on the function argument". To give an example, say you have a table of products and you want to store, for each product, which other products it is compatible with. When querying, you would like to retrieve a table where you have products in the row headings _and_ in the column headings, with either 1's or 0's (for YES or NO) in the cells. So for example, you would have printers, toner cartridges, mainboards and processors as products. There would be two distinct compatibility matrices: printers against toners, and mainboards against processors. The same problem could also occur when you want the _same_ values in the row headings and the column headings: for example, city distances. You have a table of (city, longitude, latitude) and you want to output a matrix of distances, which would have both rows and columns indexed by all cities. In this case, the graph is not a typical one - every two nodes have a connection, they only differ by the "weight" (or, in this case, distance) assigned to the connection. For storing, there seem to be about three possibilities: 1) A table with as many columns as there are rows. I don't know what is the limit on the number of columns, but as there might be _lots_ of rows, this doesn't seem like a very good idea. 2) A table with a SET column (i.e. "node int, connections set") - this might work for lots of rows but not lots of connections from one node; also, it doesn't store the "weights" of the connections. 3) A table with the columns: (node1 int, node2 int, connection int). This is probably the most general way, but also the most space- inefficient, I would guess. For retrieving, AFAIK, SQL doesn't provide any way to transform rows into columns, right? Which means that in case 3), I would have to do many queries and transform the results in a procedural language, right? I would like to pick any product and fetch the whole compatibility matrix that contains it. So, that's it. Any ideas, comments, thoughts, questions? Am I trying to solve something that is already solved? Does MySQL have "SELECT MATRIX"? :-))) Thanks for your time and brainpower. Vaclav Dvorak <[EMAIL PROTECTED]> --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
