Calculate what is missing

[Stooney]

So I feel I may have a design flaw here.

 

I've created a site where users submit reports to admins.  Reports are required to be submitted by a certain date.

 

Users are assigned clients.  Each client is assigned report types.  These are the reports the user must upload for the client.  Each report type is due X days from the start of the fiscal year it is assigned..

 

I need to somehow be able to find out which reports are missing.  I don't think I can query a database for what doesn't exist.

 

A report is missing if the due_date (number of days the report type is due from start of the fiscal year) is less than the number of days that have passed since the start of the fiscal year it is assigned to.

 

How do I efficiently determine what reports are missing from the database based on that?  I really hope I explained that well, and I'm sure I'm not going about this correctly.  All help is appreciated.

 

So I have this general design (slimmed way down for simplicity):

files table
    report_id
    report_type (used with the due dates table)
    client_id (the client the report was uploaded for)
    user_id (the user who submitted the report)
    year_id (fiscal year)

due dates table
    report_type
    due_date (number of days after start of fiscal year the report is due)

client/therapist table (list of which client is assigned to which users)
    client_id
    user_id

client required file types table (the report types required for the clients)
    client_id
    report_type

report types table (just the report type info)
    report_type (id)
    report_name

years table
    year_id
    start_date

[w3evolutions]

Are all report_type required for each client_id?

[Stooney]

In the current situation, yes.