MySQL Database Schema for role based system


We are developing a platform for NGOs (N) to get their work done via Individual Volunteers (V) or Volunteers via a Company (C)

NGO

  1. An NGO can come signup for an account and create a profile.
  2. It creates Activities (jobs it needs help from volunteers) to which Individual Volunteers or a Company (Sub set of their Employees who are registered as Volunteers in the system) can apply.
  3. An NGO can check the Applicants Profile and previous Work History and accept the application. On Acceptance they become members of an Activity.
  4. Here While they work after regular intervals say 2weeks they need to enter the amount of hours they have contributed towards that particular activity.
  5. NGO has to validate this time so that it can reflect in a Volunteers profile as credit

Company

  1. A Company signup for a profile.
  2. It uploads the list of all its employees in a particular formatted CSV file to add Volunteers against itself or send an invite link to to ask their employees to signup. If an Employees already exists in the system we send an email asking him to validate the company's claims
  3. Company can search for a particular Task and apply to it by selecting all its employes or a subset of it.
  4. While Validating time for the work done it can be done it two ways. 1 Company can centrally say V1 V2 V3 have completed 2hrs 3hrs and 2hrs and submit for validation from the NGO or allow each of its Employees handle this manually and allow them to submit it.

Where i need help?

I have created the NGO and single Volunteer relationships. I am confused as to how use the same tables but allow a new entity like Company come in between the NGO and Volunteer and manage the time validation and activity management.

The Time Validated is very important as it will be used to be shown that in the Social Equity Balance of the NGO, Company as well as Volunteers (Individual Work and also Worked for a cause through a company)

I have created the ER diagram below for the NGO and Volunteer and need to create the Company part of it.

enter image description here Link: http://i.stack.imgur.com/OMY21.png


Answers:


I'm not sure you need to change your schema much, or even at all. Your schema requires an application to go with it to make it do anything - you can't implement all the logic here - some/most of it will be in your application.

As I understand it, your spec says that all actual volunteer work is performed by individuals, some of whom may be associated with a Company and some aren't. Your schema captures this already.

That's pretty much all you need, I think. When you say:

While Validating time for the work done it can be done it two ways. 1 Company can centrally say V1 V2 V3 have completed 2hrs 3hrs and 2hrs and submit for validation from the NGO or allow each of its Employees handle this manually and allow them to submit it.

This is already covered - either each individual inputs their own work records or the application allows the company to do it for them - and then the NGO validates these records in the same way, regardless of who entered them.

I have created the NGO and single Volunteer relationships. I am confused as to how use the same tables but allow a new entity like Company come in between the NGO and Volunteer and manage the time validation and activity management.

Lets go through a worked example to illustrate both use cases, to make sure we've got everything covered:

Worked Examples

Individual, no company

An individual signs up, creating a row in the volunteers table. They sign-up for an activity, creating a row in the ngo_activity_applications table.

The NGO approves them, creating a row in the ngo_activity_members table - and either removing the row in the ngo_activity_applications table, or changing it's status - the spec. is unclear.

The individual does some work and logs the time in the app, creating rows in the ngo_activity_time_validations table.

The NGO validates the work done somehow, then tells the app this. This presumably changes the status of the rows in the ngo_activity_time_validations table and creates either one summary row or matching rows in the volunteer_validated_times table. Spec unclear where cost_per_hour comes from?

Company

A company signs up and uploads a CSV file with 3 volunteers in. This creates a row in the companies table, plus three rows in the volunteers table, and 3 rows in the company_volunteers linking table.

Company Volunteer 1 signs up to an activity individually and everything proceeds as above.

The Company signs up for a different activity and volunteers all 3 of it's people to work on it. This creates 3 rows in the ngo_activity_applications table.

The NGO approves all three, creating three new rows in the ngo_activity_members table - and either removing the rows in the ngo_activity_applications table, or changing their status - the spec. is unclear.

The volunteers do some work and the company logs time in the app on behalf of Company Volunteers 1 and 2 - and Company Volunteer 3 logs her own time:

Company Volunteers 1 and 2 The company uses the application to log the time on their behalf - creating 2 rows in the ngo_activity_time_validations table.

Company Volunteer 3 Company Volunteer 3 uses the application to log their own time - creating a row in the ngo_activity_time_validations table.

The NGO validates the work done somehow, then tells the app this. This presumably changes the status of the rows in the ngo_activity_time_validations table and creates either one summary row or matching rows in the volunteer_validated_times table. Spec unclear where cost_per_hour comes from?

Summary

You can see how much validated time any individual has logged by querying the volunteer_validated_times table JOINed to the volunteers table. You can also see how much validated time any Company has logged, by doing the same query but also joining on the company_volunteers table.

Possible changes & Questions:

You might want to add a company_entered flag to the ngp_activity_time_validations table, so that you can distinguish between records entered by individuals and ones entered by the company on an individuals behalf. You might also want to add the ID of the person who makes the entry in this table, if logging that is relevant to your application.

Might want to add an hourly_rate column to the volunteers table, to use as a default cost_per_hour when creating rows in the volunteer_validated_times table.