SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast azuranz's Avatar
    Join Date
    Sep 2005
    Location
    Bermuda Triangle
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question What is the database best practice storing Country/State location pointer?

    This is a very broad / common scenario and I'm hoping there is a best practice here.

    No doubt @r937 would know this one if he's still on these forums (I've been gone a while!)


    I have 3 database tables: Country , State & Event - described below.

    Country = list of countries worldwide,
    State = list of states worldwide, foreign key CountryID
    Event = list of events, which have a location (State/Country).

    What is the database best practice for storing a "location" pointer in the Event table?

    1. Store StateID only?
    Since state has foreign key country.

    2. Store CountryID & StateID?
    Since a Country may have no states. (Common)

    3. Store a 4th table "Location"
    1 entry for each Country-State combination,
    and at least 1 entry for Country with no state.
    azuranz
    Confucius says... man who fight with wife all day, get no piece at night

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you forgot an option --

    4. just store the state/country data in the event table with no other related tables

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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