Table Design

[Bryan Hughes]

Hello,

I have a db I am developing and I am at a impasse.

I have a agency table that stores a unique ID for each agency.
Each agency has multiple program names unique to an individual agency,
and each user is assigned to an agency but can be part of multiple programs
for that agency.
Should I create a single Program Table with Agency ID and a single User
table with Agency ID for all programs and users, or should I
create a user table for each agency and put the agency programs table under
this?

-TFTH
Bryan

 

[KARL DEWEY]

The structure below is based on a user not working for more than one agency.

tblAgency
AgencyID (PK)
Name
Other data fields

tblAgencyProgram
AgencyID (two field combination PK)
ProgramName

tblProgramUser
AgencyID
ProgramName
User
Set relation of PK to the same field in table below it.