linking payments

[James]

Hello All,

I have a membership database, members can buy examinations, membership
subscriptions and items (mugs, ties etc). I have a table linked to member
called payments which is a history of what they have paid for. There is a
seperate table for the exams they have sat, and I want to create a table for
the items they can buy.

The problem is, I need to put the object they have bought on hold till their
money has cleared, ie not send out their mug or exam till their check has
gone through. So I need to link the payments table to the exams table and
also the items table. What is the best way to do this, or am i going about
this the wrong way.

I am thinking of having 3 id fields in the payments table, one for the
member, one for the exam if that is what they are paying for, and one for the
item if that is what they are paying for.

Member id will always link to the member who is buying, examid will have a
number if they are paying for an exam or a 0, same with item id.

Is this a good way to do it? YOur comments would be greatly appreciated.

James

 

[TC]

James, it's hard to discuss database design questions, on the basis of
a natural-language description of the table structures. You'll find
that it's much better, in the long run, if you provide the definitions
in explicit detail. To do that, just list the tables, with the most
important fields that you will have in each table. Also show which
fields or combinations of fields are the primary key for each table,
and which are foreign keys to other tables.

For example:

tblProductPriceHistory
ProdID < composite < foreign key to tblProduct
DateFrom < primary key
Price

and so on.

HTH,
TC

 

[James]

ok, here is what i have so far,

tblmembers
memid <unique id
name
address
etc

tblexams
examid <unique id
memid <linked to tblmembers 1 to many
datesat
grade
resultssent

tblpayments
paymentid <unique id
memid <linked to tblmembers 1 to many
date
amount
datebanked

What i need is too other table for items, eg

tblitems
itemid <unique
description
price

tblitemsbought
purchaseid
memid <linked to tblmembers
itemid <linked to tblitems
datebought
number


What i need is for the item or exam to be held untill the money is banked.
THis works for the registrations, i have a querey based on tblmembers and
tblpayments linked by memid. All records without a date in datebanked are not
processed.

Should I add fields to payments for examid or itemid? Just to make things
more complicated some things are sent for free so no charge and no record in
tblpayments

Many thanks. james

 

[TC]

ok, here is what i have so far,

tblmembers
memid <unique id
name
address
etc

Looks good. But use the term "primary key" (PK), not "unique ID". They
ar not always the same thing. A table sometimes has a unique value
which is /not/ the primary key. For example, say you added Social
Security # to that table. You might define a unique index on that
field, to prevent two people having the same SS #. But that does not
necessarily mean that you'd define the SS # as the PK. You would
probably keep the memid as the PK. So you need to say explicitly, which
field (or combination of fields) is the PK.

tblexams
examid <unique id
memid <linked to tblmembers 1 to many
datesat
grade
resultssent

No. If a member can take many exams, and an exam can be taken by many
members, this is what you really need:

tblMembers (as before)
memid < PK
name, etc.

tblExams
examid < PK
exam name, date, etc.

tblExamMember
memid < "composite"
examid < primary key
score

tblMembers has data about each member, regardless of which exams (if
any) he's taken.

tblExams has data about each exam, regardless of which members (if any)
have taken it.

tblExamMembers has data about the attendance of a specific member on a
specific exam; for example, what score that member got, on that exam.

HTH,
TC

 

[TC]

Can different members take the same exam at /different dates/?

If so, move the exam date field from tblExams, to tblExamMember.

HTH,
TC

 

[James]

Hello,

Sorry I did not include another table called tblexamdetails. THis contains
the full name, pass mark etc. The other is full details, as they are
assignments i cannot have a record for each exam date as that would mean
hundreds of them.

My Problem is with linking the payments in.

 

[TC]

But if examid is the PK of tblExams, you can only have a single member
on each exam! Surely that can not be right?

As for "hundreds of" dates, Access can happily deal with a /million/
records, if the table structures are properly designed. Limiting the
number of records, is really not a factor that you should consider,
initially, when you design your table structures.

HTH,
TC

 

[James]

Well its based on sitting, so yes only one member can attend one sitting as
it is at their house. The other table has examcode, and exam name etc.
Therefore a exam can have many dates (ie each week) and many members, and a
member can sit an exam many times, but never on the same day.

 

[TC]

You'd need to post /all/ the tables (including a clear identification
of their primary keys) before I could comment further.

Cheers,
TC

 

[James]

Hi, I appreciate your help, but the exam system works fine. I just need
advice on if it is plausable to have three fields linking to three different
tables, when most of the time only one field will have an id in it. I have
hundreds of tables but only 4/5 are important for this.

tblmembers
memid <primary key
name
address
etc

tblexams
examid <primary key
memid <linked to memid in tblmembers
examcode <linked to another table with full details of exam
dateapplicationreceived
grade
etc

tblpayments
memid <linked to memid in tblmembers
amount
datetaken
datebanked
etc

Now at the moment only tblpayments and tblmembers are linked, so that a
member renewal does not get processed until the linked payment is cleared.
How can i do that for the exams and items? Do I need to link the items and
exams tables to payments table? Should I have three fields in each payment
record, linking it to the exams, members and items tables. Then each payment
record have a selector to point out which one it is paying for?

Am I making sense? Its really hard for me to get my head around.