Nearby Locations in a Location Dimension

Hi

I have a Location dimension that contains a Nation, State, City, Suburb hierarchy. The business has want to be able to report on activity within a suburb and its surrounding suburbs. They have defined what the nearby suburbs are for each suburb in variable length lists. I am not sure of the best way to implement this in my dimension. I do not want to simply add a new column to store the nearby suburb and replicate the row for each nearby suburb as this would make it messy to record the activity against a specific location. I am also not keen on adding multiple 'Nearby Suburb' columns eg Nearby Suburb 1, Nearby Suburb 2, Nearby Suburb 3 etc as this will make reporting difficult.

I am leaning towards a bridge table that links the Location dimension to a second, shrunken version of the Location dimension for the Nearby Locations.

The other option I have considered is an Outrigger table on the dimension.

Can anybody help determine which of these options is better or, if there is a better solution I should be considering

Thanks

Mark

Its a bridge. But all it contains is the relationship: two foreign keys. You don't create a "shrunken version" of the dimension.

Thanks for your prompt response. Would the bridge be between the Fact and the Dimension or self referential on the Dimension only please?

No reason it could not be used for both.

Thanks