Thanks that was a really good post, although some of if went overhead, I'd be
reading it over and over a couple of times. I figure this will be a complex
group of relationship in order to achieve desired affect. Instead of creating
a balance I decided to link every invoice to a number of sessions that are
also linked to session details (trainers). This way the monetary value of the
sessions are remained and I could just count up all the missed of defaulted
sessions with session attendance sheet or something. The most important part
is retaining the financial information (invoice and payment method), So
Client :: Invoice :: Payment Plan :: Sessions :: Trainer. A bunch of
validations would be placed on the Sessions part so that sessions don't
exceed certain parameters (dates, times). I figure I'd need some visual basic
knowledge to run these complex validation criteria and calculations. So that
if a trainer already has a session it can only be added under the same time
frame if that trainer is in the same location in regards to another session.
The most important thing is that a client pays for the session because if it
is not paid for then the sessions wouldn't exist,

Fred wrote:
Hello lanjoudun,

If you don't mind a blunt 30,000' view in an attempt to be helpful, your
overall posts looks like you are putting the cart before the horse.

I think that have recognized the importance of and have done some good work
on step 1, which is to shut the computer off and get organized on the nature
of the entities and information that you want to database. I say "start"
because your list of relationships raises some issues which I don't think
that you have resolved.

Your first three lines describe a triangle of relationships. I suspect
that one of these (probably clients to payment plans) may be indirect and
thus not to be recorded as a relationship in your database.

Another is that you have described 2 pairs of relationships (Clients-
classes, Trainers- classes) that are "one to many" in both directions,
which adds up to one many-to-many relations with related requirements (IF you
are documenting these relationships) for junction tables.

Another is that I think that you missing one or two types of
entities/tables. To decide that you will need to decide/clarfiy what a paid
invoices creates and entitlement for a certain total value of sessions vs.
creates an entitlement to a particular set of sessions.

Step two is a solid table structure to accomplish that. Yours needs a solid
(somewhat complex) table structure, and from your post, I think that this
(most important) stage seems to be barely on your radar screen and you are
jumping to later steps and wondering why they aren't working. Without that
you are building on swanpland and nothing will go well. If you did indeed
do these, then those structural details should/would be a part of your
questions.

Regarding sessions, if the answer to that last question is the former, then
you probably need an InstancesOfAClientReceivingASession table (of course,
shorten my long names). If it's the latter, then you probably need a
"SessionTransactions" table which has "credit" records added when they buy
sessions and debit records for instances of a client using a session.

--
Message posted via http://www.accessmonster.com