Imagine the following tables.
Person
- id
- name
Place
- id
- name
Coverage
- id
- from_person_id
- to_person_id
- new
- old
- start_date
- end_date
- closed
CoveragePlaces
- id
- coverage_id
- place_id
The rules of the application state that a "person that is covering" for another "person" will have one record in the coverage table. The coverage table contains the "from_person_id" which is a reference to the person "covering". "to_person_id" representing another "person" who is "receiving coverage". A "person" can cover for another person from a "start_date" to an "end_date" for "old" or "new" or "all" jobs and at specific "places". No single "person" can have coverage for identical places, dates, and job status (all, old or new).
As of right now, our data is stored very similar to the above. When selecting to find people currently providing coverage for another person. We would do something like this.
SELECT
coverage.to_user_id AS user_id
FROM
coverage
INNER JOIN
coverage_places
ON ( coverage.id = coverage_places.coverage_id AND coverage_places.place_id = 10 )
WHERE
coverage.from_user_id = 150 AND
coverage.new = 1
Although the rules of the application state that no person can have 2 records of identical coverage. The current state of the application allows for the record to be inserted, but it overrides the existing one once outputted.
We want to change this functionality and display to the user what coverage will be replaced by their existing coverage. Since their new coverage could potentially override multiple rows.
For example, if Person A is providing coverage for Person B at place1, place2, and place3 for new jobs this entire month.
Person A is also providing coverage for Person C at place1, and place2 for all (new/old) jobs the entire month.
Then Person C decides that Person D is covering for them for the entire month at place1, and place2 in place of Person A only for old patients.
Now the application needs to detect that Person A will still be covering for Person C, but only for old patients at place, and place2.
The records for the coverage explained above (before the record for persond is entered) is below.
coverage_places
coverage_id place_id
1 1
1 2
1 3
2 1
2 2
coverage
id from_user_id to_user_id
1 1 2
2 1 3
places
id name
1 place1
2 place2
3 place3
person
id name
1 persona
2 personb
3 personc
4 persond
Now the application needs to display the record that it is going to replace. If the user verifies that they want to override this coverage then the application needs to modify or delete the old coverage and insert the new coverage.
Also consider if Person A decided they wanted Person E to cover for them at place1 for old jobs for the next week.
We currently have a model very similar to the above working where a lot of logic is done on the programming end to find what coverage overlaps and display not just the record that overlaps but the pieces of that record that overlap. The code has become very complex. I have decided to step back and take a look at things to see if there may be a more simple / efficient approach.
Also bear in mind that in the future the application hopes to support dynamic fields for coverage. So i can create a new field "job pay" and have another person cover for certain pay amounts.
Any ideas or suggestions would be greatly appreciated.
