Skip site navigation (1) Skip section navigation (2)

Two Tables That Share Data?

From: Carlos Mennens <carlos(dot)mennens(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Two Tables That Share Data?
Date: 2012-02-08 20:34:38
Message-ID: CAAQLLO41psLcO4-u45JtPC=4JP5cbWohHR32_z__wN2U1=GCcw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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(at)myco(dot)tld
  2 | Configuration Management | cm(at)myco(dot)tld
  3 | Facility                 | facility(at)myco(dot)tld
  4 | Software Development     | software(at)myco(dot)tld
  5 | Finance                  | finance(at)myco(dot)tld
  6 | Logistics                | logistics(at)myco(dot)tld
  7 | Inventory                | inventory(at)myco(dot)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(at)myco(dot)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?

Responses

pgsql-novice by date

Next:From: Philip CoulingDate: 2012-02-08 21:06:08
Subject: Re: Two Tables That Share Data?
Previous:From: Pushpendra Singh ThakurDate: 2012-02-08 06:15:13
Subject: Re: Remote connection issue

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group