locked
Table Relastionship RRS feed

  • General discussion

  • hi.

    i have designed a database that stores information from several people in a table knwon as GeneralIfo.

    and these people have educational degree from different countries so there will be 3 other tables : Education , Country , City

    with the following relation :

    - Person (
    Id int not null, 
    CityId Foreign Key refereces City(Id)
    )
    - Country(
    CountryId Primary key int , not null 
    ,CountryName)
    - City(
    CityId primary key int , not null
    CountryId Foreign Key references Country(Id)
    )

    my problem is:

    when i want to have the information of the educated person's County and city i don't know whether to join CityId (FK refrences to Education Table) or CountryId(FK refrences to Education Table) or even to join both of them to Education Table?

    Tuesday, May 22, 2012 5:08 AM

All replies

  • Hmm I would consider having City and County in the Person table...

    But in your example I would have in City table CountryID column referencing to Country table to be able join with Person table


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Tuesday, May 22, 2012 5:17 AM
  • Actualy i have CountryId column in City table .

    but i didn't get what you mean exactly ???

    what will happen to Education table ? should i have CityId column in Education table or CountryId or both of them?

    Tuesday, May 22, 2012 5:38 AM
  • I do not see Education table here? Looks like I missed that  you have CountryID column

    So JOIN with Person table would be

    SELECT <columns> FROM Person JOIN City ON......

                                                         JOIN Countries ON Countries.CountryID =City.CountryID 


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Tuesday, May 22, 2012 8:00 AM
  • thank you so much Uri.

    Tuesday, May 22, 2012 9:27 AM