Help with relating tables

[new7media]

I need help with constructing tables for an application. I have a staff list that includes the following fields:

first name
last name
department
title
phone

What we want to do is make it so that you can view this list in its entirety or by sorting on one of these fields. But some of these folks belong to more than one department. And some folks have more than one title depending on their department.

At first I was thinking I'd set up a table that holds the first name, last name and phone # info since that's always the same, and then I'd create a second table that holds department info. Then I'd do up a third table that relates these two tables to each other (id, person_id, department_id).

What's throwing me is the title data since some folks don't have a title at all, or they have a title in one department but not another.

How would you construct this? Would you expand the table that relates everything to include a field for title (id, person_id, department_id, title)? Is that "legal"?

Any help is most appreciated! Thanks!

[Dvector]

You could do it in one table

Table: people_db
person_id
first_name
last_name
department
title
phone

Supposing you have only a certain number of departments and titles, you could use the column type SET to allow more then one deaprtment and title to be in that particular field.

SET('department1','department2', . . .)
SET('title1','title2', . . .)
http://dev.mysql.com/doc/mysql/en/set.html for more info

You could also do it in two tables

Table: people_db
person_id
first_name
last_name
phone

Table: people_departments_db
id
person_id
department(SET)
title(SET)

Its kind of up to you in the end whether you want more tables in your database.