Table Design - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Table Design

Author

Topic

mohdmartin
Starting Member

22 Posts

Posted - 2007-09-17 : 05:57:13

I create following tables.

employee table
------------------------
EMP_ID varchar
NAME varchar
DEPARTMENT_CODE int
POSITION_CODE int
COUNTRY_CODE int

department table
------------------
DEPARTMENT_CODE int
DEPARTMENT_NAME varchar

position table
---------------
POSITION_CODE int
POSITION_NAME varchar

country table
-------------
COUNTRY_CODE int
COUNTRY_NAME varchar

In employee table I have to store two country names.
ie. employee currently working in this country
but his home country name (origin country) is another
name. In country table all countries information available.
ie. country_code and country_name.

How I can design the employee table that it get country_name from COUNTRY TABLE ?
but we can not use same country_code in two fields.

OR
otherwise I will have to create employee table like this

employee table
------------------------
EMP_ID varchar
NAME varchar
DEPARTMENT_CODE int
POSITION_CODE int
COUNTRY_CODE int
COUNTRY_NAME varchar

ie. during data entries direct insert the country_name in employee
table ?

I want to create separate table for separate data.
department,postion,country.

How I can use unique country_code in employee table's two
fields ?

regards
Martin

Kristen
Test

22859 Posts

Posted - 2007-09-17 : 11:29:00

I would have two columns in the EMPLOYEE table:

WORK_COUNTRY_CODE
ORIGIN_COUNTRY_CODE

and join each of these to the same COUNTRY table:


SELECT *
FROM EMPLOYEE
    LEFT OUTER JOIN COUNTRY AS WC
        ON WC.COUNTRY_CODE = WORK_COUNTRY_CODE
    LEFT OUTER JOIN COUNTRY AS OC
        ON OC.COUNTRY_CODE = ORIGIN_COUNTRY_CODE

Kristen

mohdmartin
Starting Member

22 Posts

Posted - 2007-09-18 : 04:07:48

Thanks you very much,
it is very helpfull

Martin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources