Database Design

[jgnasser]

Database deisgn

I have been given a task to create a small database to keep track of the activities of our small group in which the members give contrinutions per month and borrow money. One table I'm sure I need is 'Members' which shall have details of the members. When it comes to recording the transaction for each member each month, having the members table with enough fields to record the transaction month by month sounds so inneficient and against the spirit of good design. This would also mean I need to add new fields every month to enter transactions for each member. This is the kind of info I will keep for each member:

AmountpaidJan2004
AmountborrowedJan2004
TotalAmountSavedAsAtJan2004
AmountpaidFeb2004
AmountborrowedFeb2004
....
....
. all these as fields to take data for each member.

Without actually solving the whole problem for me,could someone kindly give me hints or post similar sample databases?

 

[neileg]

This is a fundamental feature of relational databases like Access. You should hold a primary key field in your members table. This uniquely identifies the member. Then you need a second table for transactions. Each tranasction should hold the primary key of the member it relates to. You can therefore hold an infinite number (well up to Access file limits) of transactions for any individual. Use of queries forms or reports will enable you to extract the data from each table so that you can show the full picture for each member.

 

[R6Flyer]

I would echo Neil's sentiments but also add that your transaction table needs to look like:

tblTransaction
TransactionID (PK autonumber)
MemberID (FK Long integer)
TransDate (Date type)
AmtBorrowed (currency)
AmtPaid (currency)

This way you can log as many transactions against each member as you want without the need for creating additional fields (you can group and sum the amounts borrowed and paid by month in the report that you will subsequently create).

 

[jgnasser]

Thanks all for the input so far. I'll give this a try and see how it goes.