New to database design, and looking for an elegant way to track patient follow-ups at a non-profit mental health clinic. Specifically, we need to know which patients are due for a 90-day consultation, and these can extend out indefinitely for as long as the patient is active. It would obviously be easy if we just limited it to a certain number of periods, but theoretically patients could need 90-day follow-ups for years and years.
So we have a patient.IntakeDate, and need to know which patient in any given month is coming up on another 90-day interval from their last one. So, for instance, I would want to search the date range of 12/1/2010 - 12/31/2010, and know which patients' are due in that time frame. Creating a hundred different fields of the IntakeDate + 90, + another 90, and so on seems crude and ridiculous, so I'm sure the experts out there have a better idea.
Even though the existing database is Filemaker Pro 11, I am open to switching to either SQL Server or MYSql, so anything relevant would be helpful.
Thanks in advance for your expertise!