Re: Database design question

I would suggest you start with normalizing your design entity emp (empid, ...)
entity role (roleid, ... )
entity activity (actid, ...)
entity step (stepid, ...)

and for the relations which are many-to-many:

emp-role (empid, roleid, ....)     pk=(empid,roleid)  empid=fk1  roleid=fk2
role-act  (roleid,actid, ....)         pk=(roleid,actid)  roleid=fk1 
 actid=fk2
act-step (actid, stepid, ....)        pk=(actid,stepid)  actid=fk1   

the dots can be attributes like perhaps a start / end date any given employee has certain role(s)
if you want to list the steps for an emp you will have to join all 7 tables

If the number of steps per emp is very limited you can consider to create an unnormalized entity
with empid, roleid, actid , stepid. No need to give it a separate pk if the combination is unique.

Carlos Flor wrote:

>Hello! I am fairly new to DB design and I have run into a problem. I
>have a list of employees for a company. Each employee can have
>several roles. EAch role is composed of several activities and each
>activity is composed of several steps. Now, each step can belong to a
>number of activities and each activity can belong to a number of
>roles, etc... Is the best way to do this to have separate tables for
>step, act, role, and employee and then have a new table that would
>like like: newPK, stepID(fk), actId(fk), roleID(fk), empID(fk)? I
>can't figure out how I can form relationships between all of this
>data. I want to be able to take a person and be able to find out what
>act's they have, or what roles, or what steps and any combination of
>those things. Can anyone help? Thanks and i hope that makes sense.
>
>

-- 
Anton Versteeg
IBM Netherlands