Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1

    Question Unanswered: Data Type For Coding Dept?

    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?

  2. #2
    Join Date
    May 2008
    Posts
    277
    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
    Last edited by futurity; 02-08-12 at 23:28.

  3. #3
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1

    Question

    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.

  4. #4
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    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.

  5. #5
    Join Date
    May 2008
    Posts
    277
    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.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by CarlosinFL View Post
    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?

Posting Permissions

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