Structure M:M

SQL Apprentice Question
What is the best way to create a M:M relationship between a column and
itself.

For example I have a table of suburbs and I would like associate with each
suburb it's surrounding suburbs which effectively will be a M:M relationship
with itself ?


CREATE TABLE Suburbs
(
SuburbID INT IDENTITY PRIMARY KEY,
Suburb VARCHAT(50),
State VARCHAR(3)
)


CREATE TABLE Surrounds
(
SuburbID INT,
NeighbouringSuburbID INT
)


This is what I have come up with however I am sure there is a better way.



Celko Answers
>> the M:M table toaccurately record this:


(Suburb1, Suburb2)
( Suburb2, Suburb1)

Surely this relationship should be able to be recorded using a single
entry
without duplication ? <<


Never use IDENTITY and always look for a standard code -- USPS or
Census would be good for this. Let's get the DDL right first


CREATE TABLE Suburbs
(merto_code INTEGER NOT NULL PRIMARY KEY,
suburb_name VARCHAR(30) NOT NULL,
state_code CHAR(2) NOT NULL);


CREATE TABLE Neighbors
(metro_code_1 INTEGER NOT NULL,
metro_code_2 INTEGER NOT NULL,
CHECK (metro_code_1 < metro_code_2 ),
PRIMARY KEY (metro_code_1, metro_code_2));


Now for the answer, use a view.


CREATE VIEW Surrounds (metro_code_1, metro_code_2)
AS
SELECT metro_code_1, metro_code_2
FROM Neightbors
UNION ALL
SELECT metro_code_2, metro_code_1
FROM Neightbors;


You will need an INSTEAD OF trigger for updates, inserts and deletes.
This will prevent one-way relationships, hide teh details fromt he
users, etc.