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?