Stuck on table design

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I have a system I'm building.

There are several applications.  For each application there are one OR two
reviewers.  For each reviewer, there is a team leader, and for each team
leader there is an administrator.

Applications primary key is ApplicationID.  Two reviewers can edit their
own application at any time until they submit it to the team leader.  At
that point the team leader can send it back to the reviewer, make changes
or send back to the reviewer, or approve and send to the administrator.

Can anyone suggest a table which would allow me to check for, at any given
time, who controls the "application?"  The reason I'm running into a wall
is because there is 1 application, but two reviewers scoring that
application; both scores must be saved, and reviewer A must be able to send
to the team leader without affecting reviewer B's ability to continue to
edit/score.

I've come up with this:

AppID       int     // refers to application table
admin       int     // refers to the personnel ID in personnel table
teamlead    int     // same
reviewer    int     // same
owner       enum("admin","teamlead","reviewer") // actually is a tinyint that refers to a "role" table for full normalcy

For each application, and for each reviewer, a row would be added.  The
reviewer would have full control until the "owner" field changed to
"teamlead".  This is the only way I can think of to do what I'm looking
for.

I also have an "assignments" table, where I have:
    AppID
    TeamMemberID
    roleid

so I know for each application, a person is assigned a role.  There can be
multiple reviewers, so there is no unique key on these columns.

Any other suggestions?

---------------------------------------------------------------------------
Peter Beckman                                                  Internet Guy
beckman@purplecow.com                             http://www.purplecow.com/
---------------------------------------------------------------------------

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux