Please I need your advice, I’m working on a membership-based contributory loan scheme. I need best practice to be in effect, hence my seeking assistance here. Please note that I have initially seeked assistance regards the loan aspect of the DB (http://www.dbforums.com/microsoft-ac...recordset.html) but felt that my Design wasn’t the best hence my second coming. Also, in line with forum etiquette, I have made a similar post earlier at Access Help and How-to - Microsoft Office Database Design Guidance! - UtterAccess Discussion Forums Requirements:
(i)Members make contributions on a monthly basis
(ii)Members can also make Deposits and Withdrawals monthly (Multiple deposits & withdrawals possible within a month and Balance = (Deposit – Withdrawal) to be added to current balance in contribution table)
(iii)Members can assess loans (Multiple Loans)
(iv)Dividends will be paid to members based on some relations between Income, Expense etc.
(v)A Committee of five members (Also must be members of Co-operative) with powers to authorize membership and have a tenure of 3 years
(vi)Referees must be members of Co-operative too.
What I have done
I initially created the tables as in Model A
In Model A, I have grouped all the member details (required at the Member form level) into one table and used it to generate the Member form.
KEY: CTB==Contribution| Ref==Referee|NK==NextOfKin| tblMembers
autoMemberID|txtLastName|txtFirstName|txtMiddleNam e|txtUnit|txtGender|txtMobilePhone|txtHomePhone|tx tEmail|txtBank|AccountNo|SortCode|txtNextOfKinName |txtNKMobile|txtNKHome|txtRelationship|txtAddress| txtCity|txtState|datDateJoined|txtRefereeLastName| txtRefereeFirstName|txtRefereeMiddleName|txtRefere eRank|numRefereeID|txtRefereeUnit|datRefereeSignDa te|txtAuthorization|txtStatus|datAuthDate|txtRemar k|txtNote|attPassport|boolIsActive
autoCTBID|MemberID|datCTBDate}curCTBAmount(CA)|cur ShareAmount(SA)|dblShareValue|curThriftAmount(TA)| curWelfareAmt(WA)| [curDeposit| & curCurrentBalance(CB)|]=calculated fields to be removed!
NB: CTBDeposit = CA – (SA+TA+WA) & CTBBalance = CA - WA are calculated fields and in the light of the advice that they should not be stored, I’ll create a query and then show their values in a report.
Based on my reading on normalization principles (I'm not too sure how to go about this and if what I have done is okay)
I’m thinking that tblMembers should be broken down to some extent as proposed in Model B
Relationship in tblNK will still be a lookup field, is it okay?
AuthName will lookup to the FullName in qryFullName.
RefName, RefID, & RefUnit will also lookup to respective fields in tblMembers.
Thinking to leave out the tblPhones & tbPhoneTypes altogether and still have them in tblMembers.
tblMembers (Contains all fields except the fields below, which are lookup fields in Model A)
tblUnits > UnitID|Unit
tblGenders >GenderID|Gender (Male|Female)
tblPhoneTypes > PhoneTypeID|PhoneType (Mobile| Home etc)
tblAuthorization >AuthName|Status|AuthDate| Remarks (Approved|Not Approved|Pending)
Which format is better? Because I want all this information on the member form. Please note that in Model A, Units/Genders/Banks/Authorizations(Name of Officer)/Status(Position)/Remarks are lookup fields but based on the article “http://access.MVPs.org/access/lookupfields.htm”, I’m thinking of moving them to their own tables as shown in Model B and rather do a Lookup to the Tables.
(When this is done, I can't edit the values in the lookup tables at the form level and since users won't have access to the tables independently, this becomes an issue).
Or should I go the way of Model B and create a query to join the different tables together, then use the query to create the Member form.
*Thinking this might not augur well due to the many tables that will be involved.
Model B, then at the point of form creation, under design mode, select the fields from the different tables to generate member form.
Regards tblAuthorization (members with power to authorize membership), These persons have a tenure of 3 years, and hence every 3rd year, the membership of this group will change; how do design such that when the committee changes, i.e should still be able to query the members (and see authorizations done by them if need be) of this committee in Tenure A, Tenure B, Tenure C and so on
Also, note that the Unit in tblUnit & tblRef are the same.
Regards tblRef: should I just use RefName like NKName instead of breaking it down into the individual components since there will be no much emphasis on Referee name.
Regards Req. 2, I have created a transaction table for this:
The challenge is how to pull all fields in the CTB table (as shown in cont.jpg) plus sum of each Monthly deposits as a field and sum of monthly withdrawal of Transaction table into a query! I’m okay with CTBDeposit and CTBBalance (also part of query fields) and so that this can be shown on a report as Total Contributions.
NB: The attached ERD is a modified form of Model B