Jump to content

Calculate what is missing


Stooney

Recommended Posts

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.