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