MySQL Database Schema for role based system -

we developing platform ngos (n) work done via individual volunteers (v) or volunteers via company (c)

ngo

1. an ngo can come signup account , create profile.
2. it creates activities (jobs needs volunteers) individual volunteers or company (sub set of employees registered volunteers in system) can apply.
3. an ngo can check applicants profile , previous work history , accept application. on acceptance become members of activity.
4. here while work after regular intervals 2weeks need enter amount of hours have contributed towards particular activity.
5. ngo has validate time can reflect in volunteers profile credit

company

1. a company signup profile.
2. it uploads list of employees in particular formatted csv file add volunteers against or send invite link to ask employees signup. if employees exists in system send email asking him validate company's claims
3. company can search particular task , apply selecting employes or subset of it.
4. while validating time work done can done 2 ways. 1 company can centrally v1 v2 v3 have completed 2hrs 3hrs , 2hrs , submit validation ngo or allow each of employees handle manually , allow them submit it.

where need help?

i have created ngo , single volunteer relationships. confused how use same tables allow new entity company come in between ngo , volunteer , manage time validation , activity management.

the time validated important used shown in social equity balance of ngo, company volunteers (individual work , worked cause through company)

i have created er diagram below ngo , volunteer , need create company part of it.

link: http://i.stack.imgur.com/omy21.png

i'm not sure need change schema much, or @ all. schema requires application go make - can't implement logic here - some/most of in application.

as understand it, spec says actual volunteer work performed individuals, of whom may associated company , aren't. schema captures already.

that's pretty need, think. when say:

while validating time work done can done 2 ways. 1 company can centrally v1 v2 v3 have completed 2hrs 3hrs , 2hrs , submit validation ngo or allow each of employees handle manually , allow them submit it.

this covered - either each individual inputs own work records or application allows company them - , ngo validates these records in same way, regardless of entered them.

i have created ngo , single volunteer relationships. confused how use same tables allow new entity company come in between ngo , volunteer , manage time validation , activity management.

lets go through worked example illustrate both use cases, make sure we've got covered:

worked examples

individual, no company

an individual signs up, creating row in volunteers table. sign-up activity, creating row in ngo_activity_applications table.

the ngo approves them, creating row in ngo_activity_members table - , either removing row in ngo_activity_applications table, or changing it's status - spec. unclear.

the individual work , logs time in app, creating rows in ngo_activity_time_validations table.

the ngo validates work done somehow, tells app this. presumably changes status of rows in ngo_activity_time_validations table , creates either 1 summary row or matching rows in volunteer_validated_times table. spec unclear cost_per_hour comes from?

company

a company signs , uploads csv file 3 volunteers in. creates row in companies table, plus 3 rows in volunteers table, , 3 rows in company_volunteers linking table.

company volunteer 1 signs activity individually , proceeds above.

the company signs different activity , volunteers 3 of it's people work on it. creates 3 rows in ngo_activity_applications table.

the ngo approves three, creating 3 new rows in ngo_activity_members table - , either removing rows in ngo_activity_applications table, or changing status - spec. unclear.

the volunteers work , company logs time in app on behalf of company volunteers 1 , 2 - , company volunteer 3 logs own time:

company volunteers 1 , 2 company uses application log time on behalf - creating 2 rows in ngo_activity_time_validations table.

company volunteer 3 company volunteer 3 uses application log own time - creating row in ngo_activity_time_validations table.

the ngo validates work done somehow, tells app this. presumably changes status of rows in ngo_activity_time_validations table , creates either 1 summary row or matching rows in volunteer_validated_times table. spec unclear cost_per_hour comes from?

summary

you can see how validated time individual has logged querying volunteer_validated_times table joined volunteers table. can see how validated time company has logged, doing same query joining on company_volunteers table.

possible changes & questions:

you might want add company_entered flag ngp_activity_time_validations table, can distinguish between records entered individuals , ones entered company on individuals behalf. might want add id of person makes entry in table, if logging relevant application.

might want add hourly_rate column volunteers table, use default cost_per_hour when creating rows in volunteer_validated_times table.