Hi all,

I have to create a database where the elements of a table are related to
elements on the same table. What is the best (optimised) way to do that?

I came with the model:

CREATE TABLE locations(
location_id int(11) auto_increment,
location_name varchar(50) NOT NULL default '',
UNIQUE KEY idx1 (id)
) type MyISAM;

CREATE TABLE relations(
relation_id int(11) auto_increment,
location_id1 int(11) NOT NULL,
location_id2 int(12) NOT NULL,
distance float NOT NULL default '0',
UNIQUE KEY idx1 (location_id1, location_id2)
UNIQUE KEY idx2 (relation_id)
) type MyISAM;



And the query will be like:

SELECT location_name, location_name, distance
FROM locations AS l, relations AS r
WHERE l.location_id=r.location_id1
AND l.location_id=r.location_id2;


Thanks for your help
Pedro


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=m...ie.nctu.edu.tw