Reconstrucing my app -- Advice on this proposal

Get Help Database
1

I’m creating a fairly simple app. The scope is as follows:

  1. My business is tracking a number of new teams added each month within each country.
  2. Registered users will need a quick entry list with a drop-down for their list of countries that they manage only.
  3. So the entry form just needs to track the:
  • Month/Year of reporting (they could essentially post prior months missed if needed so they are not necessarily reporting the month of the current date).
  • Country (from filtered drop-down)
  • Number of new teams for that period

  1. I would like to restrict a user from entering data for a period once they have already submitted for that country/month/year so there isn’t duplicate data. If possible I’d allow them a screen to update entries if needed as well.

  2. I would eventually add some reporting screens for cumulative totals from all countries, totals by country, totals from all countries per month, and perhaps other options. But reporting is secondary at this point.

Any thoughts on the based table structure to handle this?

2

I’ve made some headway that I am happier with so let me address my own point and continue to ask for assistance on other pieces.

  1. I am easily able to achieve a simple form to capture the data.

  2. I have this portion working easily after looking through some other posts and videos.

  3. I’m using a separate table for the Month Names and one for Years. I’m linking them both for the Teams collections that I am recording. This method seem like the best and easiest way for all of this.

  4. Here is where I’d like some more control. Each user will report an entry each month for any new numbers for each country. If there is no new teams, they would still report a 0. So there should be a row for each country in each month of each year. But I only want 1 row to exist for each country in each month of each year. It would be nice to have a dynamic menu that as you select the year and the month, the country drop-down would be limited only to those countries that have no entry for that period. Or at the least a returned error that data already exists.

Another thought I had was having a page with a simple list instead of a dropdown for the countries. So you would still have a drop down for year and month but then a dynamic list that would only show the names of the countries needing an entry. Then clicking on the name of a country would link to another form with the year, month, and country already filled in and just awaiting the additional number of teams field before submitting.

Any possibilities with either of these scenarios?