Re: Two Tables That Share Data?

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).