Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2011
    Posts
    2

    Smile Table Design Issue

    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?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    using the ISO 3 digit code is a good design call, others may have been tempted to use an autonumber column

    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).
    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'
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2011
    Posts
    2

    Smile

    @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.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •