Well, that's not how your join table should work. A record in a join table should link a single payment to a single session. To link one payment to several sessions, you need a join record for each session that all link to the same payment.
Thus, your session payment table might have records like this:
Each row is one record in the join table
Session Payment Fraction Payment 1 60 1 1 100 2 1 300 3 2 40 1 2 200 2 3 200 3
(Seems odd that you didn't apply all of payment 1 to session 1, then all of payment 2 to session 1, then split payment 3 over all three sessions to show them as paid in full, but that's a business issue, not a database issue.)
Here's a many to many demo file you can examine. The two main tables are named "contracts" and "companies", but if you rename them "sessions" and "payments" you'll have the structure you need here. Note how portals to the join table are used in this demo file and feel free to follow up with more questions after you examine this file.
The numbers I used are arbitrary and don’t reflect the reality of what I from a business perspective.
What you have laid out as the join table I have already imported into the payment table. In other words, I have the equivalent of the six records that you had mentioned, right now, resident in the payment table (which, by the way, is really about 13,000 records). The question I have is, what portion stays in the payment table ( and what would be the architecture in that? for example would the full payment one ($100) show up as one record?) and what portion goes into the join table and what portion stays in the session table (would the charge of $460 stay as one record in the session table?) Do I define the parent tables as having the primary keys and the join table as having the foreign keys? Do I have to manually import into the session payment table? Do I get that through portals? Complete newbie here. sorry.
Really appreciate your taking the time out to answer this.
By the way I couldn’t open your demo file. Any instructions that you can give me on that?
Sounds like you imported data into the payments table that should be imported into the join table. Once that data is imported, you could use a script to produce matching records in either payments or sessions if you don't have data to directly import into those tables.
Were you able to download the right file? (Click blue the download button, ignore the other buttons on the download page, wait 20 seconds for download link to appear, click it to download the file.)
Each record in payment should represent one payment. Referring to the join table example, you would have 3 payment records with amounts of 100, 300 and 500 respectively. The join table links a given payment record to a given session record and then specifies what fraction of the total payment is applied to the bill for that session.
Sessions::SessionsID = Session_Payments::SessionsID
Payments::PaymentID = Session_Payments::PaymentID
As you'll see in the demo file, you can place a portal to the join table to list data from both the join table as well as the other related table. A portal to Session_Payments on a payments layout can list all sessions to which the payment was applied. A portal to this same table on Sessions will list all Payment records linked to that session. Comparing the session cost to the total of Session_Payment payment fractions will tell you if a particular session is "paid in full" or not.