Table Design

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bryan Hughes - 28 Jul 2005 20:15 GMT

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

Reply to this Message

KARL DEWEY - 28 Jul 2005 22:49 GMT

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.

> Hello,
>
[quoted text clipped - 11 lines]
> -TFTH
> Bryan

Reply to this Message