Results 1 to 10 of 10

Thread: country

  1. #1
    Join Date
    Oct 2006
    Posts
    29

    country

    I attach herewith a screenshot of the country, city, province, state and constituent table. From a front end point of view when I select a country from a combo box, the next combo box will be populated with either the city or province or state or constituent details.

    The problem is with the member table. I can have a countryId as a foreign key in the member table. But how do I associate the member table with either the city or province or state or constituent table?.

    Your help is kindly appreciated.

    Thank You.
    Attached Thumbnails Attached Thumbnails tables.JPG  

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    add the city,province and state to the member table
    .. thats if you want the ability to find people by each constituent part. easily.. ie in a where clause.
    ..however the possible problem you may have with this approach is that its very US centric, now if your app is going to be used in the US or places that follow the US address style all well and good,, but its going to be painful to use elsewhere.

    or navigate up and down you table structure using appropriate joins.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2006
    Posts
    29
    Therefore what would you propose?. This will be used internationally.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    The US has
    Country, State, Country
    the UK has
    Country, County, hwoever some people may also what to include Scotland or Wales
    eg: UK, Scotland, Lothian, Edinburgh
    Bear in mind that not all postcode are the same format, some use all numeric (Zip Code (US) Cedex (France) some use alphanumerics (UK, Canada), soem dont' use any postal code, some may or may not have a postal code eg Ireland, Australia)

    its is a source of problems if you try to impose one address model on others, ie a US address model doesn't work in a UK context.

    as said before you could store the country, state and province ID's in the member table
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Oct 2006
    Posts
    29
    well i will not be collecting the street address and the postal code.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    You should not use separate country, city, province, state, and constituent tables.
    You should model these in a single recursive table called "Region", allowing members to be associated at any level.
    Sorry healdem, but I would strongly argue against adding city, province, state, etc to the members table. That is just asking for referential integrity issues.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Oct 2006
    Posts
    29
    Single recursive table called "Region". How will I know which is the country, city, province, state, and constituent? In this case country will be the parent and city, province, state, and constituent will be the child. Is it something like this:

    Region Table
    ------------
    regionId (PK)
    description
    subRegionId

    Therefore specifically for country can the regionId and subRegionId be of the same value?.
    Last edited by solomon13000; 01-22-10 at 12:02.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Region Table
    ------------
    regionId (PK)
    RegionType (Country, State, County, Zip, City, Yard....)
    description
    parentRegionId
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Oct 2006
    Posts
    29
    I attach herewith an image of the refined tables with data.
    Attached Thumbnails Attached Thumbnails tables.JPG  

  10. #10
    Join Date
    Oct 2006
    Posts
    29
    Therefore in the members table do I include the following attribute which is the regionId and the parentRegionId?.

Posting Permissions

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