Data Type For Coding Dept?

CarlosinFL · #1 (**permalink**) 02-08-12, 15:27

I want to create a separate table in my database called 'dept' and basically identify each unique department in my company represented by numeric code for example:

Code:

CREATE TABLE dept
(
id SERIAL PRIMARY KEY,
name VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(50) UNIQUE NOT NULL
);

So this table should look something like:

Code:

 id |           name           |       email        
----+--------------------------+--------------------
  1 | Information Technology   | it@myco.tld
  2 | Configuration Management | cm@myco.tld
  3 | Facility                 | facility@myco.tld
  4 | Software Development     | software@myco.tld
  5 | Finance                  | finance@myco.tld
  6 | Logistics                | logistics@myco.tld
  7 | Inventory                | inventory@myco.tld
(7 rows)

Now I'm going to make a new table called 'employees' & there is going to a field called 'dept' which will have a value from the 'id' field in the 'dept' table. My question is how does one traditionally configure this in SQL?

When I create my employee table, what data type do I use to create the 'dept' field? It will only be storing a low numerical value since I only have less than 20 physical dept records. Do I need to create a foreign key constraint against this? I'm expecting the data to look like this:

Code:

 id |    name     | dept |      email      |    hire    
----+-------------+------+-----------------+------------
  1 | James Smith |    5 | jsmith@myco.tld | 2011-04-19
(1 row)

As you can see the user James Smith is in dept. #5 which is my finance dept. Can someone please point me into the right direction on how to get this database working as mentioned above?

futurity · #2 (**permalink**) 02-08-12, 16:51

Generating surrogate keys is not something that's covered by the SQL standard, consequentially each database system has their own way of doing it.

PostgreSQL uses a non-standard SERIAL data type, which is not a true data type, but merely syntactic sugar that creates a sequence, creates an integer column on the appropriate table, and then uses the sequence as the column's default value (all of which IS standard SQL). In fact if you examine your table in psql (by invoking "\d <tablename>"), this is exactly what you'll see. This is all explained in the documentation.

If this is at all similar to other DBMSes, then you need to find out the underlying data type of the identity column and use that as the data type in foreign keys. For PostgreSQL:

serial -> int
bigserial -> bigint

CarlosinFL · #3 (**permalink**) 02-09-12, 10:24

So I have my two following tables:

Code:

zoo=# \d dept
                                 Table "public.dept"
 Column  |         Type          |                     Modifiers                     
---------+-----------------------+---------------------------------------------------
 id      | integer               | not null default nextval('dept_id_seq'::regclass)
 name    | character varying(50) | not null
 email   | character varying(50) | not null
 manager | integer               | 
Indexes:
    "dept_pkey" PRIMARY KEY, btree (id)
    "dept_email_key" UNIQUE CONSTRAINT, btree (email)

zoo=# \d employees
                                 Table "public.employees"
 Column  |         Type          |                       Modifiers                        
---------+-----------------------+--------------------------------------------------------
 id      | integer               | not null default nextval('employees_id_seq'::regclass)
 fname   | character varying(50) | not null
 lname   | character varying(50) | not null
 email   | character varying(50) | not null
 dept    | integer               | 
 manager | integer               | 
 salary  | numeric(8,2)          | not null
 hire    | date                  | not null
Indexes:
    "employees_pkey" PRIMARY KEY, btree (id)
    "employees_email_key" UNIQUE CONSTRAINT, btree (email)

So I'm looking for an example SQL statement or a way to query the 'employees' table and show the users actual managers name rather than the reference dept.id field INTEGER. Right now the data in my 'employees' table looks as follows:

Code:

SELECT * FROM employees
WHERE id =1;
 id | fname  |  lname  |      email       | dept | manager |  salary  |    hire    
----+--------+---------+------------------+------+---------+----------+------------
  1 | Carlos | Mennens | cmennens@zoo.com |    4 |       4 | 89001.25 | 2006-02-01
(1 row)

If I want to get a result from employees.manager to show 'John Smith' rather than the numerical # 4, how would a statement like this look? I understand PostgreSQL uses some non-standard SQL data types but regardless there has to be a way to do something like this and I'm just trying to learn how in the most standard SQL way.

JarlH · #4 (**permalink**) 02-09-12, 10:39

In SQL there are almost always several ways to solve a problem.

In this case you can have a three-way join where you use the employees table twice, the first time to find the employee data and the second time to find the manager data. Of course you have to go via dept to find the manager id.

select *
from employees
join dept ON employees.dept = dept.id
join employees AS managers ON dept.manager = managers.id
where conditions

Or you can put a sub-select in the main select-list to return manager name:

select employees.*,
(select fname ||lname from employees managers join
dept ON managers.dept = dept.id
where employees.dept = dept.id)
from employees
where conditions

Note that a sub-select like this isn't allowed to return more than one row, if it does an error is raised.

Both ways are Core SQL-2003 compliant.

futurity · #5 (**permalink**) 02-09-12, 11:15

Carlos,
I'm assuming you have foreign keys implemented in your tables? employees.dept should be a foreign key to dept.id, and employees.manager should be a foreign key to (presumably) employees.id.

Just a slight modification to Jarl's second example:

Code:

select
    employees.*,
    (select fname || ' ' || lname
        from employees as manager
        where employees.manager = manager.id
    ) as manager_name,
    (select name
        from dept
        where employees.dept = dept.id
    ) as dept_name
from employees
where ...

Though I don't see any reason to not just use a simple inner join like in his first example.

tonkuma · #6 (**permalink**) 02-09-12, 21:04

Quote:

Originally Posted by CarlosinFL

So I have my two following tables:

Code:

zoo=# \d dept
                                 Table "public.dept"
 Column  |         Type          |                     Modifiers                     
---------+-----------------------+---------------------------------------------------
 id      | integer               | not null default nextval('dept_id_seq'::regclass)
 name    | character varying(50) | not null
 email   | character varying(50) | not null
 manager | integer               | 
Indexes:
    "dept_pkey" PRIMARY KEY, btree (id)
    "dept_email_key" UNIQUE CONSTRAINT, btree (email)

zoo=# \d employees
                                 Table "public.employees"
 Column  |         Type          |                       Modifiers                        
---------+-----------------------+--------------------------------------------------------
 id      | integer               | not null default nextval('employees_id_seq'::regclass)
 fname   | character varying(50) | not null
 lname   | character varying(50) | not null
 email   | character varying(50) | not null
 dept    | integer               | 
 manager | integer               | 
 salary  | numeric(8,2)          | not null
 hire    | date                  | not null
Indexes:
    "employees_pkey" PRIMARY KEY, btree (id)
    "employees_email_key" UNIQUE CONSTRAINT, btree (email)

So I'm looking for an example SQL statement or a way to query the 'employees' table and show the users actual managers name rather than the reference dept.id field INTEGER. Right now the data in my 'employees' table looks as follows:

Code:

SELECT * FROM employees
WHERE id =1;
 id | fname  |  lname  |      email       | dept | manager |  salary  |    hire    
----+--------+---------+------------------+------+---------+----------+------------
  1 | Carlos | Mennens | cmennens@zoo.com |    4 |       4 | 89001.25 | 2006-02-01
(1 row)

If I want to get a result from employees.manager to show 'John Smith' rather than the numerical # 4, how would a statement like this look? I understand PostgreSQL uses some non-standard SQL data types but regardless there has to be a way to do something like this and I'm just trying to learn how in the most standard SQL way.

Why employees table have manager column?

Is the "manager of a person" "manager of the dept" to which the person belongs?

Internet Services

Web Development

Digital Marketing

Consumer Tech