DB Design Help 1

[puitar]

Hello,

I build websites for a real estate company.eg

http://www.prd.com.au

The client wants to incorporate a "surrounding suburb" search when searching for properties.

eg When a user searches for real estate within a suburb the search results will include all real estate in nearby suburbs as well.

I'm just not sure how this data should be structured.

I have an existing table with about 2000 suburbs.
The client will manage the "surounding suburb" data.
I need to build an interface for the client to update this data ( ASP.NET )

Should the db design be a many-many ( suburb - suburb ) relationship or should it be ( Suburb - suburb1,suburb2,suburb3 etc )

Which would be easier to manage?

Thanks

 

[johnherman]

I would use a many to many. The bridge table would only consist of two columns, the keys to the suburb table records.

It will require a little extra work so that your location routine would recognize that Suburb A (column 1) being nearby to Suburb B (column 2) is the same as Suburb B (column 1) being nearby to Suburb A (Column2).

So if you wanted all the neighbors of Suburb A.
Select Column1 from NeighboringSuburbsTable where Column2 = 'Suburb A'
Union
Select Column2 from NeighboringSuburbsTable where Column1 = 'Suburb A'

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[puitar]

Thanks johnherman,

All is good.