Hi

I am doing a school project which involves storing a large amount of data.he project involved storing info about each country (in a Country table with PKey country's ISO3 code) and multiple statistics.

One data-set is about storing country and its neighboring countries. I am thinking of creating a table with 2 columns (country1 code, country2 code along with a auto-incr pkey column) which stores this info: For e.g. US, Mexico; US, Canada etc. But this leads to a large amount of data duplication (e.g. Mexico;US).

Can you suggest a better way to store this info?

using the ISO 3 digit code is a good design call, others may have been tempted to use an autonumber column

what you have is an adjacency model.
I don't think you need the auto increment column. storing pairs is good enough in my books
There will be a problem of data duplication if you allow the users to put in information willy nilly. I'd suggest in your front end you control how these adjacency rules are defined by forcing the pairing to be in alphabetic order, and use both columns as the primary key and both columns indexed
fer instance
looking at Central Europe And the Czech republic
Austria - Czech
Czech - Germany
Czech - Poland
Czech - Slovakia

when you search for adjacency your where clause will have to look in both columns
eg
Adjacencies
Country1
Country2

SELECT my, column, list from mytable
JOIN <some join criteria>
WHERE country1 = 'GBR' or country2 = 'GBR'

@healdem

Thanks for the sugg. I am new to databases and am trying to understand the best way to implement this. I can implement the retrieval part, but wondering what would be the best way to insert data if I follow your approach.

For instance, assume I get data like this: country, list<bordering countries>. How do I go about doing the insertion without retrieving the existing data?

I don't want to use procedures/PL-SQL kinda stuff as I will be moving this data-set later to Google Big Table, and I want to do this migration with minimal impact.

One thing you might consider is a CHECK constraint to force the value of the country1 column to be less than the value of the country2 column. This would guarantee that entry was consistant and would avoid duplicate entries, but you would still need to search both columns unless you knew the value of both columns.

-PatP