Primary Key, Normalizing?

I am a beginner, so please bare with me. I get very confused on how to normalize my database.

Firstly: The employees in the company I work for are in various departments and can have more then one title and work in more then one department.

Example: John Smith can work in the engineering department as a detailer and an engineer and at the same time work as a project manager for the management department.

How do I setup this table structure?

Employees Table
Login (PK) | First | Last | Extension......
--------------
jsmith | John | Smith | 280

Department Title Breakdown
Department | Title
--------
Engineering | Detailer
Engineering | Engineer
Management | ProjectManager

Job Description
Login | Title
--------
jsmith | Engineer
jsmith | Detailer
jsmith | ProjectManager

This is important to break this down because for each project the following is saved:

Project Listing
Project | Detailer | Estimator | Sales | Engineer |...... | Location
10001 | jsmith | jdoe | mslick | sjunk | ..... | Las Vegas

Or should the project be broken down as well

Project Listing
Project | Location
10001 | Las Vegas

Project Team
Project | Member | Activity
10001 | jsmith | Engineer
10001 | mstevens | Detailer

Any thoughts on how to normalize this?

Mike BMikeB,
As far as I can tell you have a decent start on the process and relationships. To me, the key determinant of whether you proceed to separate out the various "step owners" for a particular project is how stable the steps to your projects are. If you have always had an Estimator, a Detailer, an Engineer and a Salesperson for every project and every project has just one person doing that particular task, then it's only extra work to break the relationship out into a separate table.

BUT, if ever there has been a situation where more than one person has done the Estimating (for example) for a project, or if you think you might be adding roles for Quality Assurance and Documentation Specialist in the near future, then it's worth the time to break the relationship out into a separate table.

Look at your business and try to foresee what things might be like six months from now. If in your mind's eye things aren't going to change, then avoid the extra work and invest it somewhere else.

But that's just my opinion (and I'm lazy).

Regards,

hmscott

Edit note: Apparently, I'm so lazy that adding an "n't" is just too much work. Sigh.|||when you say 'Normalize', what do you mean?

Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. I used to get argued about this one quite a bit,, it actually means 1NF)

How far do you want to go?

BoyceCoddNF or 3NF or higher

the true trick to normalization is to consider the pimary key as the parent within the table iteself in a relationship with the other attributes(cols)
if a col has a many to one relationship to the key, and it isnt a shared dependency with another column in the table. then you are on the right track to 3NF and BCNF|||[quote]
and it isnt a shared dependency with another column
[quote]

What is meant by a shared dependency?

Let's say I have a Company Table

Company
ID (PK) | Name | Address | City | State | ....

The City and State would be repeated many times, therefore, not dependent and can be many keys to one city / state. This is what you mean correct?

Should be broken yet even further.

Company
ID (PK) | Name | Address | Postal (FK)

Zip
Postal (PK) | City | State

Mike B|||i dont know what i was thinking
i meant functional dependency

this is where one item is dependent on another for its definition.
the dependency must hold true for every possible value of the item
currently and in the future
++++++++++++++++++++++++++++++
Product , Price , Weight
rock, 1.00, 1#
rock, 1.00, 1.5#
Rock, 1.00, .75#
brick, .75, 2.0#
brick, .75, 2.3#
stone, .85,.25#
marble, 45., 15.#
++++++++++++++++++++++++++++++
price for brick is always .75 even if the weight changes.
price is functionaly dependent on product
reperesented as (Product) -> Price
and actually product is fd on price because each individual product has only one price.
so that would be (Price) -> Product

if the price of rocks went up based on the weight (example,,, all rocks that weigh 1.5# will now cost $1.50

now price is no longer functionally dependent on product alone now the FD is represented as
(Product, Weight) -> Price

this is something that you should be aware of and consider highly while designing your tables but remember that the relational rules are good ones but are in no way a law "more of a guideline than anything"|||Originally posted by hmscott
or if you think you might be adding roles for Quality Assurance and Documentation Specialist in the near future, then it's worth the time to break the relationship out into a separate table.

Very very possible. Thanks for you comments.

Mike B|||Originally posted by Ruprect
i dont know what i was thinking
i meant functional dependency

Thank you for your comments. I am looking at this, and to tell you the truth, I cannot see one area where this is a problem. Time to take out the magnifying glass and get a closer look :)

Mike B