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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-novice by date

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