Table Relationship Help
I'm having some trouble wrapping my head around how many tables I need.
I have a Member database. Each member can purchase a membership, Gear or private lessons, or pay Misc Fees. I keep track of those in the Receipts table connecting those through Member # ID.
I'd like to keep track of the Memberships (when they expire, and who is the account holder), and Private Lessons (keep track of when each lesson is used) and then gear and fees.
I wanted to use a Receipt Number to track the memberships and lessons, but I'm not sure how to connect these tables to the member - whether I use the member ID, or a receipt no.
The idea is to keep track of all the memberships separately, so I know when to send out renewal emails, and also to see all the purchases from the members layout using a portal to receipts.
Under the Receipts table, I have a choice for memberships, lessons, gear or fees. And within the Receipts Table I wanted to issue a unique number (Like Type+Initials+No+date) and use that in other tables to keep track of Membership and also Lessons.
So my trouble is, I'm wondering if I should track all types of purchases under the Receipts table, or if I'm better off using more tables, and figuring out how to connect them, so that if I choose a member ID, I see only memberships in the membership table, and lessons in the lesson table, based on what feels to me like "magic", right now. Any ideas? I'm good with easy. It doesn't have to be automated.
I'm very grateful for any help or thoughts.