I am coming back to DB design after almost 20 years away and have a challenging conundrum.
For our Temple, we have a long contact list. Some contacts are alive, others have passed on.
We have a Columbarium where people buy niches when alive and designate whose ashes are to be kept there. Often these individuals are still active members, but eventually they come to take their spot.
At the same time, we have monthly memorial services for all those who passed away in a given month. Each memorial has at least one or two contacts that are notified of the upcoming event. These contacts also become subjects of memorials as the time goes on.
The many-to-many relationship of the niches was not a problem as I can indicate who purchased, who passed away and who was inurned from the related tables.
For the memorials I can have any number of members be the contact for the memorial of a deceased member, and cease to be so once they pass away themselves. I amt trying to wrap my head around how I can
1. Look up a name of a deceased member and see their contacts
2. Run a report that shows all the passed members who are being commemorated that month, along with their contacts (which could be listed in more than one instance)
I tried looking into groupings, nested tables, queries and passed values, but I am stuck.
Any suggestions for how to structure this and where to look for samples or videos would be greatly appreciated.
For our Temple, we have a long contact list. Some contacts are alive, others have passed on.
We have a Columbarium where people buy niches when alive and designate whose ashes are to be kept there. Often these individuals are still active members, but eventually they come to take their spot.
At the same time, we have monthly memorial services for all those who passed away in a given month. Each memorial has at least one or two contacts that are notified of the upcoming event. These contacts also become subjects of memorials as the time goes on.
The many-to-many relationship of the niches was not a problem as I can indicate who purchased, who passed away and who was inurned from the related tables.
For the memorials I can have any number of members be the contact for the memorial of a deceased member, and cease to be so once they pass away themselves. I amt trying to wrap my head around how I can
1. Look up a name of a deceased member and see their contacts
2. Run a report that shows all the passed members who are being commemorated that month, along with their contacts (which could be listed in more than one instance)
I tried looking into groupings, nested tables, queries and passed values, but I am stuck.
Any suggestions for how to structure this and where to look for samples or videos would be greatly appreciated.