Обсуждение: Two Tables That Share Data?

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?

On 08/02/2012 20:34, Carlos Mennens wrote:
> 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?
>
You should keep the data types the same.  SERIAL is in fact INTEGER so
dept should be INTEGER.

You do not need the foreign key, but it's a good idea.  Foreign keys add
a little overhead as they
have to be checked when you insert rows.   However foreign keys are a
good idea.  The provide a
lock against bad data being entered into the database (employees in a
department that doesn't
exist or a department being deleted while it still has employees).

On Wed, Feb 8, 2012 at 4:06 PM, Philip Couling <phil@pedal.me.uk> wrote:
> You should keep the data types the same.  SERIAL is in fact INTEGER so dept
> should be INTEGER.

OK I have my 'employees.dept' field type set to INTEGER but I'm still
trying to understand how I can perform a SQL statement that will allow
me to query the 'employees' table and visibly see employees.manager =
'Phill Collins' rather than it's assigned numerical INTEGER. Below are
both tables described:

                                Table "public.managers"
 Column |         Type          |                       Modifiers
--------+-----------------------+-------------------------------------------------------
 id     | integer               | not null default
nextval('managers_id_seq'::regclass)
 name   | character varying(50) | not null
 email  | character varying(50) | not null
 dept   | integer               |
 salary | numeric(8,2)          | not null
 hire   | date                  | not null
Indexes:
    "managers_pkey" PRIMARY KEY, btree (id)
    "managers_email_key" UNIQUE CONSTRAINT, btree (email)


                                 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)

Is there a way I can query the employees table and have the SQL
statement resolve the INTEGER value from 'employees.manager' to
display the data in 'managers.name'?

On 9 February 2012 17:33, Carlos Mennens <carlos.mennens@gmail.com> wrote:
> OK I have my 'employees.dept' field type set to INTEGER but I'm still
> trying to understand how I can perform a SQL statement that will allow
> me to query the 'employees' table and visibly see employees.manager =
> 'Phill Collins' rather than it's assigned numerical INTEGER. Below are
> both tables described:

Try something like this:

SELECT e.fname AS emp_fname, e.lname AS emp_lname, m.name AS manager
FROM employees AS e
INNER JOIN managers AS m ON e.manager = m.id
WHERE e.salary = 12345.67;

--
Michael Wood <esiotrot@gmail.com>

On Thu, Feb 9, 2012 at 11:28 AM, Michael Wood <esiotrot@gmail.com> wrote:
> Try something like this:
>
> SELECT e.fname AS emp_fname, e.lname AS emp_lname, m.name AS manager
> FROM employees AS e
> INNER JOIN managers AS m ON e.manager = m.id
> WHERE e.salary = 12345.67;

I'm going to try this now but while I was working on this in pgAdmin3
query builder tool, I got the results I wanted which was to just query
my employee table and translate the employees.manager field from
INTEGER to their actual name referenced in 'managers.name' as show
below:

psql (9.1.2)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

zoo=# SELECT
  employees.id,
  employees.fname,
  employees.lname,
  managers.name AS manager
FROM
  employees,
  managers
WHERE
  employees.manager = managers.id AND employees.fname = 'Carlos';
 id | fname  |  lname  |    manager
----+--------+---------+---------------
  1 | Carlos | Mennens | Phill Collins
(1 row)