Results 1 to 1 of 1
  1. #1
    Join Date
    Oct 2013

    Unanswered: Database Structure Assistance

    Hi all,

    Please I need your advice, Im 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 ( but felt that my Design wasnt 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
    (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.

    Model A
    KEY: CTB==Contribution| Ref==Referee|NK==NextOfKin|
    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, Ill create a query and then show their values in a report.

    autoTransID|MemberID|TransDate|TransType|TransRefe rence|Deposit|Withdrawal|

    autoLoanID|numMemberID|txtLoanType|curLoanAmount|n umInterestRate|datLoanDate|numNumPayments|curMonth lyPayment|curInterestPaid|boolIsActive|txtNote

    autoScheduleID|numLoanID|numPayment#|datPayDate|cu rBeginBalance|curAmountDue|curAmountPaid|curRegula r|curExtra|curEndBalance


    My Thoughts
    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)
    Im 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.

    Model B
    tblMembers (Contains all fields except the fields below, which are lookup fields in Model A)
    tblUnits > UnitID|Unit
    tblGenders >GenderID|Gender (Male|Female)
    tblPhones >PhoneID|MemberID|PhoneTypeID|PhoneNumber
    tblPhoneTypes > PhoneTypeID|PhoneType (Mobile| Home etc)
    tblBanks >BankID|Bank
    tblNextOfKins(NK)>autoNKID|numCHSID|NKName|NKMobil ePhone|NKHomePhone|Relationship|Address|City|State
    tblReferees>RefLastName|RefFirstName|RefMiddleName |RefRank|RefID|RefUnit|RefSignDate
    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, Im 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! Im 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

    Attached Thumbnails Attached Thumbnails tran.jpg   cont.jpg   m.jpg   M2.jpg   M3.jpg  

    M4.jpg   ERD.jpg  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts