Re: Table Design - stuck!

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Phil:

The basic principles appear sound. Charges and payments are sub-types of
type transactions, for which a one-to-one relationship is the correct model,
with the primary key of the referencing table in each case also being a
foreign key. This of course rules out the use of an autonumber as the key of
a referencing table, but the key of the referenced table (transactions) can
be an autonumber.

Members are also a sub-type of type contacts, so again a one-to-one
relationship is the correct model, but in this case you have it the wrong way
round; you have contacts referencing members, whereas it should be members
referencing contacts as the former is a sub-type of the latter. So MemberID,
as well as being the primary key of members, should also be a foreign key
referencing the primary key of contacts, and the MemberID foreign key column
in contacts should be deleted.

Ken Sheridan
Stafford, England

Philip Herlihy wrote:
I'm the treasurer of a Badminton Club. I want to create a database
which will record which members monthly subscriptions are due, who has
paid, and which will also keep track of what the club's bank account
balance should be.

I've ended up thinking I should have a single table of transactions.
However, some transactions are payments (a cheque from a member, or a
payment to our coach) and some are "charges", e.g. a batch of appended
records to indicate that the November monthly subscription is due for
each member.

A screenshot of my relationships diagram is available here:
http://bit.ly/2uvhxn
(the latest in a series of structures).

It can't be right! When I generate forms nothing quite works!

To explain the thinking behind it:

All Transactions are made with Contacts. Most of my Contacts are
Members (for whom I store additional details) but a few are not: the
Bank, our Coach, for example. So I thought I'd try and create a
one-to-one relationship between Contact and Member - if the Contact
isn't a Member then the foreign key in the Contacts table is null. I've
indexed the foreign key MemberID in the Contacts table so that
duplicates are not allowed but Nulls are ignored. (Yes, I do wonder if
an "optional" one-to-one relationship isn't a tautology!)

On the left-hand side of the diagram, I've tried to show that
Transactions can be a Payment or a Charge (respectively, an incoming
cheque, or a batch of appendsto make the November subscription payable).
There's a "Transaction Type" field in the Transactions table to
distinguish the two types.

You won't be surprised to know that I haven't tried working with this
type of relationship before (I can anticipate some mirth at my
attempts). It's probably crackers, but the attempt is prompted by a
history of programming in Object-oriented languages, and I was trying to
factor out the general (all these objects are Contacts) and the more
specific (some Contacts are Members). Similarly, a Transaction might be
a Payment or a Charge.

If this is possible, and I can get it right, I'd hope to be able to
process a cheque from a member into the system (tap details into a
single form) and use different queries to list what each member
currently owes, to predict what the club's bank balance should be, and
even to list the number and total value of cheques I haven't paid in yet
(so I can fill in the payment slip).

I'll be grateful for any advice, both in the specifics of what I'm
trying to do, and those principles which I clearly haven't grasped yet!

Phil, London

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

.

Relevant Pages

  • Senate Housing Bill Requires eBay, Amazon, Google, and All Credit Card
    ... Senate Housing Bill Requires eBay, Amazon, Google, and All Credit Card ... Companies to Report Transactions to the Government ... this week, would require the nation's payment systems to track, ...
    (alt.marketing.online.ebay)
  • Re: Korean bank Moves back to Mainframes (...no, not back)
    ... wanted to do payment transactions on their server ... ... the effort is now frequently called "electronic commerce". ... we were invited to participate in the x9a10 financial standard ...
    (bit.listserv.ibm-main)
  • Re: NO down payment?
    ... tax credit for that down payment, ... I never went to rock camp, but in 1986 I got a chance to meet with four of the members of Menudo. ... A friend of mine was part of the opening magic act for Menudo. ... He was a young magician. ...
    (misc.invest.stocks)
  • Re: Payments Schedule
    ... members table called "members initialisation date" showing a date from which ... I could schedule payments for that member. ... category" showing the type of membership payment preferred,e.g. ...
    (microsoft.public.access.gettingstarted)
  • Re: Georgia Lawmaker wants to require gold, pre 65 silver for tax payments
    ... from any person or entity as payment of any obligation to the state ... including, without limitation, the payment of taxes; ... all other transactions within the state upon mutual consent of the ...  If I paid a Silver Eagle, ...
    (rec.collecting.coins)