I've got this problem i've been mulling over for ages and can't seem to come up with an answer for it:

I want to create a db that stores country information so i'll probably have a country tables as follows:

countryid countryname etc
1 france
2 germany
3 spain


I also want to store information about what country borders another country. To my mind this is a self referencing many to many relationship so i create a borders table to resolve the many to many as follows:

countrya countryb
1 2
1 3

the problem with this is the relationship borders is symmetrical ie. if france borders germany then germany should also border france, yet with this design france can border germany without germany bordering france.

any thoughts, ideas, comments, etc. would be most welcome.

thanks

j

your "self referencing many to many relationship borders table" is fine

all you have to do is make sure that whenever you store a pair of country FKs, the lower one goes first

this lets you avoid storing the relationship twice

hi rudy,

many thanks for your quick reply... i'm afraid i'm going to show my ignorance here:

how does having the lower fk go first help me?

i'd be happy to tell you right after you explain in a bit more detail what your problem was again?

Another way to do this is to name or number each border and treat the border itself as a set (ie. the set of adjacent countries):

CREATE TABLE Borders (BorderNum INTEGER NOT NULL, CountryNum INTEGER NOT NULL, PRIMARY KEY (BorderNum, CountryNum));

This has the possible advantage that there is only one column in which to look for a country.

In your join table you will have columns for CountryA and CountryB.
Which table comes first can be arbitrary, though if you use Rudy's suggestion of enforcing a constraint against CountryB ever being a lower value than CountryA you can prevent duplicate relationships such as:

...not that such duplicates would be a huge issue, as long as you UNION the table to itself to get the entire set of relationships from either direction, and use this UNION view in all your code joins:

Yields:
This result set would effectively show you all bordering countries for every country.

If you just want to enforce the constraint that borders must be symmetrical then you could do this:

CREATE TABLE Borders
(countrya INTEGER NOT NULL,
countryb INTEGER NOT NULL,
PRIMARY KEY (countrya,countryb),
FOREIGN KEY (countryb,countrya) REFERENCES Borders (countrya,countryb));

INSERT INTO Borders VALUES (1,2),(2,1);

that's a cute trick, but it more or less locks you in to double entries

(by the way, in which database systems would inserting the (1,2) fail because the (2,1) isn't there yet? does this technique require suspending FK checking until after the transaction? this is getting messier by the minute...)

wow, this is some forum, thanks for all your replies... i think i'm getting left behind here :-)

sorry, rudy, yes i think your previous reply has sunk in.. keeping the lower id first prevents duplicate entries, right??

my problem then is how do i successfully query the db to tell me who borders germany when france borders germany but germany doesnt border france... if you see what i mean??

i guess blindman's union qry does this for me??

i can see that this would solve the problem but it kind of feels a bit wrong - a bit too border centric - i'm probably mixing the db design with the end application here - but this design feels like it would force me to be adding countries to borders, whereas i'd envisaged adding a country neighbours to countries, if you see what i mean?

SELECT countrya FROM borders WHERE countryb = 'germany'
UNION ALL
SELECT countryb FROM borders WHERE countrya = 'germany'

yeah,

in your/anyone's opinion is it better to, or what are the advantages/disadvantates of :

a) using a union qry for the data retrieval, & storing lower fk first in the borders table to prevent duplication.

compared with

b) automatically adding duplicate entries to the border table by adding the opposite entry ie. (2,1) & (1,2) or (4,3) & (3,4) every an entry is made

any thoughts, comments, etc. much appreciated,

cheers

j

One of the goals of normalization in database design is to reduce or eliminate duplicate data. (If you have duplicate data, what happens when you update/delete the first row, but forget to update/delete the second??? After the change, if you don't know the answer already, how do you then know which row is correct?)

After all, it's very easy to add a check constraint to the table, so that CountryA must be less than CountryB. (and, in a case like this, where once entered, the table data will change rarely, it's not as if there would be a performance hit...)

Agreed. I'd never implement something like this, though it is interesting.

Yes. Create my union query as a view, and you won't have to think about it again.

Select * from UNIONVIEW where CountryA = 'Germany'

That is all you would need.