Suggestions welcomed on this?

[SilvorMike]

Hi everyone, I hope you are well.

Come across this forum as a way of looking for the best solution to store, edit and output the values in a DB. 

I run a charity at Christmas time and we get lots of referrals in to support children and adults.

I want to keep a list of ages 0-15 and then 16+, against sex "Male","Female"

The list ultimately will show me how many of each age against each sex.

I want to publicly display this information. Then I want people to sign up and they will enter a quantity against the age/sex they want to help.

So they may have a grid
Age   |   Male | Female
1
2
3
4
5
etc...

They'll increase the number against Female aged 3 and Male aged 10 to say they'll help these people. Once they submit, the main website will calculate the new value against who's committed to helping.

I.e. if we have a row in another table, the sum of male aged 10 will reduce from the requirement for male aged 10 and tell me how many are left still needing help. 

Embarrassingly, I am used to PHP and have developed PHP systems myself. But this is an important project that I just don't want to get wrong. I want it to be efficient and I thought, may aswell ask what you guys thing first on ideas how best to achieve this?

I'm also not sure I've ever done a form that uses a grid format like this in the past?

What is the best method to output the count in the same grid format above, I guess a function would do it best? 

Thank you so much - as you can see, totally rabbiting on but want to get this working in the most efficient way and there's so many clever people out there.

Thank you 🙂

 

[ginerjm]

To simplify your input form and the resulting 'inventory' form I would keep them entirely separate.  Let the users check the inventory with one form that queries the db and outputs a very simple screen that most easily can be done with an html table.  Then give them a button that takes them to your 2nd form that lets them choose from a drop down of ages, and a dropdown of sex and updates the db with their choices.

I assume that your db has one table that will have an age column and a sex column and the user id of any person who has made a commitment.  Also you may have a table of those people who have committed that gives you contact info as well as the user id.  So - 2 tables.

Does this sound like something you can do?

[Barand]

32 minutes ago, SilvorMike said:

I.e. if we have a row in another table, the sum of male aged 10 will reduce from the requirement for male aged 10 and tell me how many are left still needing help.

That is what you don't want to do. Don't increase or reduce values in tables. Instead store individual referals and individual offers. The number still required will be the difference between total referals and total offers which can be calculated when required by a query.

So if you you had a db structure similar to this

+-----------------+                                          +----------------+
| organisation    |                                          | volunteer      |
+-----------------+                                          +----------------+
| org_id          |----+                                     | volunteer_id   |----+
| org_name        |    |                                     | name           |    |
| contact         |    |                                     | address        |    |
| etc.            |    |                                     | phone          |    |
+-----------------+    |                                     | etc            |    |
                       |      +------------------+           +----------------+    |       +----------------+
                       |      | referal          |                                 |       | help_offer     |
                       |      +------------------+                                 |       +----------------+
                       |      | ref_id           |                                 |       | help_id        |
                       +-----<| org_id           |                                 +------<| volunteer_id   |
                              | referal_date     |                                         | offer_date     |
                              | sex              |                                         | sex            |
                              | age              |                                         | age            |
                              | name             |                                         | quantity       |
                              | address          |                                         +----------------+
                              | etc              |
                              +------------------+

... then counting the referals by sex/age gives the requirement and summing the quantities of help offered by sex/age gives the aid total. The deferrences are how many you still require.

[SilvorMike]

Hi there

Thank you so much for the guidance. It really helps having more than one idea on this as I want to get it right.

It makes sense what both of you have said. At least it gives me a foundation to start thinking about finding time to get this in to code and in to practice. 

It'll make the management around this time of year much easier. It's a very much manual process at the moment and I want to make it better for next year.

Thank you very much for your replies.

[gizmola]

To really help you I think we need a better understanding of the basics of your problem.

Do I understand this correctly that you have a table of persons.  I could argue that this application would benefit from some form of the party model, but for simplicity, as Barand did, let's assume there is a table of persons, and a table of helpers.

What does the "persons" table look like?  Is there a row for each person who will receive help along with a birthday or age?  

Conversely, the helpers are individuals and not organizations or some mix of both?  Their commitments are to "help" a particular number of "persons" and this help for some reason has to be typed by gender AND age?  Is there no option for an organization to help either/or?

So as a "helper" I have to go through this convoluted UI and indicate:  I will help "3 females of the age of 2", "1 male the age of 7" etc?  

What is the nature of "help".  Is this providing items/clothing/toys?  Why is the age important.  Do age ranges come into play, and if so how, or why not?