UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
3 Pages V  1 2 3 >  (Go to first unread post)
   Reply to this topicStart new topic
> Normalization Question, Access 2007    
 
   
DeborahSV
post Jan 29 2018, 12:51 PM
Post#1



Posts: 103
Joined: 14-September 10



First, many thanks to all the contributors here. I was recently directed to the Newcomer's Reading List and spent hours reading about database design and normalization. I am totally self-taught in Access and learn a bit from each project I try, but that collection really clarified a lot for me.

I am about to start a new, big (for me) project, and even after all my reading, I'm not sure I'm planning the design properly.

This database is for a tax-exempt religious charity organization that offers its recipients loans. (The database is only for the loan program, not for any of its other charitable activities.) I charted all of the different "entities" and the ways they're related, but I'm stuck.

Here is the basic info: The charity has donors that give permanent donations, and it also has contributors who "lend" money - i.e. they give money that the charity organization can lend to needy people, but they will take their money back at some point. They call the latter contributors "Fund Holders", and record the name of that person's fund within the general charity. (And one person can have more than one "fund" to be used for different kinds of loans.) The loans are given in a single check, and the borrowers must present headchecks at the time of the loan according to a preset schedule to pay back the loan in full, and the signatures of cosigners. There are fees to be paid with each loan (e.g. $xx per loan, $x per headcheck) as well as penalties that arise (e.g. bounced check, replace check, etc.) To further complicate matters, sometimes a loan is given from a particular "fund". The database also has to record expenses incurred (e.g. stamps, printer toner/paper, etc.)

Here are my thoughts on tables so far:
1. Master (includes all people's names - they can be donors, fund holders, borrowers, cosigners, etc.) (Is there a better name for this table? The charity organization is currently using a very outdated program written in 1990 in VPro5 that does some of the necessary functions [the rest they currently do by hand], and it's called Master there.)
2. Fund
3. Loan
4. LoanCosigner
MasterID would be a FK in tables 2,3,and 4; LoanID would be another FK in 4.
5. LoanFeeAssessment
6. LoanPenaltyAssessment
LoanID would be a FK in 5 & 6.

Then I have to deal with the actual transactions (all transactions are done by check - no cash or credit card).

I thought of having separate tables for the checks of donations, loans, loan payments, fund contributions, etc, but then I think I would need union queries to generate the monthly reports that they'll need to show all incoming and outgoing money for the month. It would also make deposit reports more complicated. (I didn't build deposits into the schema yet because I first need to figure out how I'm recording the checks.)

It would seem wise to me to have one Transaction table to cover all incoming and outgoing checks, with a TransactionTypeID field that will relate to a separate table, TransactionType. Transaction types will include: Loan Payment, Fee Payment, Penalty Payment, Donation, Fund Contribution (this is the temporary contribution of a fund holder), Loan, Fund Return (this is the return of fund money to the contributor/fund holder), Maintenance Expense. (The first 5 are all "credits" - i.e. money that will be deposited in the account, and the last 3 are "debits" - checks that will be written out). But the problem with this method is that I can't have one field that covers the relationship to a "parent" table, because each transactionType would have a join to something else. A loan check and loan payment check would both relate to the "loan" table, but fund contribution and fund return would join to the fund table. I could add a few fields: TransactionLoanID, TransactionFundID, TransactionFeeAssessmentID, etc. - but each one would necessarily be blank for certain Transaction types. From what I've read, that's not advisable. (?)

This is where I'm stuck.

I thought of a many-to-many junction table, but I don't think it solves my problem of needing to join to different tables.

I hope I gave enough information and would be very grateful for guidance! I don't want to start making tables until I have a plan that works.

ps - I wrote 2007 for the version because that's what I have now, but I do plan on updating to whatever's most current (2016?) before starting this project. I don't think that would make a difference, but I thought I'd mention it.
Go to the top of the page
 
tina t
post Jan 29 2018, 02:12 PM
Post#2



Posts: 5,614
Joined: 11-November 10
From: SoCal, USA


QUOTE
2. Fund
3. Loan
4. LoanCosigner
5. LoanFeeAssessment
6. LoanPenaltyAssessment

will you post the names of the fields contained in each of these tables, and describe how you envision using each table - what will any specific record describe, in each table?

hth
tina
Go to the top of the page
 
projecttoday
post Jan 29 2018, 02:53 PM
Post#3


UtterAccess VIP
Posts: 10,361
Joined: 10-February 04
From: South Charleston, WV


Is your design based on the VPro5 system? What is VPro5? What does it not do that you want to add to it?

This organization isn't in a large east coast city by any chance, is it?
Go to the top of the page
 
DeborahSV
post Jan 30 2018, 12:40 PM
Post#4



Posts: 103
Joined: 14-September 10



Hello,
I am attaching a Word doc in which I tried to explain my proposed tables and my question in more detail.

I cannot base my design off of their existing VPro5 system because it doesn't do everything they need. For example, she currently records fees and penalties with paper and pencil and creates all monthly/yearly reports for the Board of Directors manually. She currently can't delete certain types of entries - it's a very old program and the person who made it for her is no longer living. The charity is actually functioning extremely well for decades in this old-fashioned but meticulous, hybrid computer/pen-and-paper environment, but they are looking to create a database that does everything they need and that can move forward with them. Frankly, I have no idea what VPro5 is. I just saw how she uses it on her computer (DOS based, not Windows) and am trying to make something workable in Access. And no, the organization is not on the East coast.
Attached File(s)
Attached File  Database_Plan.zip ( 11.11K )Number of downloads: 21
 
Go to the top of the page
 
MadPiet
post Jan 30 2018, 01:49 PM
Post#5



Posts: 2,602
Joined: 27-February 09



(How to say this in understandable English???)

You're dealing with an IS-A hierarchy. (Different types of the same basic entity, but the different types can participate in different relationships). Rebecca Riordan wrote an article on it a long time ago - well worth a read. It's here: http://access.mvps.org/access/tables/tbl0013.htm
Go to the top of the page
 
tina t
post Jan 30 2018, 02:15 PM
Post#6



Posts: 5,614
Joined: 11-November 10
From: SoCal, USA


QUOTE
From the Word doc: Some loans are given from a specific person’s fund. We will need to check that a person has enough money in his fund to make the loan. Likewise, before returning funds to fund holders, we will need to be sure that the money is not currently out in a loan.

hmm, not sure i'm following this. so if person A lends funds of $2000, and person B lends funds of $5000, and person C lends funds of $750, you don't say you have $7750 available for potential borrowers - and then three persons can borrow $2000 each? instead, you need to track which lender's funds were given to which borrower? and if lender A wants $1000 of his fund returned to him, you can't do it until one or more borrowers from his fund have paid back a combined total of at least $1000?

did i miss the boat with the above scenarios? if so, can you clarify, pls?

hth
tina
Go to the top of the page
 
DeborahSV
post Jan 30 2018, 03:23 PM
Post#7



Posts: 103
Joined: 14-September 10



Tina, you got it right. It seems like a funny system, but that's how they do it. But only about 1/4 of the loans are given from the funds of specific people. Most loans come from the collective "pot", and likewise, most funds can apply to all loans collectively. So your first scenario covers 75% of the activities. And, I don't think the part that you wrote in the "instead" sentence is so hard. As long as I put a FundID FK in the loan table, I can easily query the database to see how much is loaned out from that fund and how much is currently available from the fund total to return. (I would need sums on 4 transaction types related to that fundID to do this: (sum(fund contribution) - sum(fund return)) - (sum(loan) - sum(loan payment [deposited])) tells me how much the Fund Holder can currently withdraw.
I think I have the logic on how to query the database to get all the reports and functionality they need; my problem is what is the best way to relate the entities to one another. I know how they're related in theory - just not sure on best practice for creating tables that will lend themselves to all the queries that I need.

MadPiet, thanks for the link. It says there: "One solution to this problem is to include all of the attributes of all the types in a single table. At first glance, this seems a simple, straightforward solution. But it breaks down quickly as the system adds additional types, with additional fields. Also, the user interface can get ugly very quickly." She seems to indicate that the only problems with this method are bloating the table and the possibility of messy ui. I read about the 1-to-1 joins, and I think I get it. However, if I don't think either of these concerns apply to this db (1. they've been doing the same thing for three decades, so they probably won't add more types, and there aren't so many attributes altogether, and 2. the ui as I envision the program will have separate subforms for each transaction type), is there any other reason not to do it? Is that called "not normalized"?

Finally, as I wrote at the end of the word doc, would there be any gain/loss to making separate tables for each type of transaction altogether? Personally, I'm partial not to go that way because I'm not fond of union queries, but I want to weigh the pros and cons of each method before I begin.
If I am told that the truly best way to go is what you call the "IS-A" hierarchy, then I will. But if a method in my current "comfort zone" is acceptable, that seems safer to me.

Thank you all for your time.
Go to the top of the page
 
tina t
post Jan 31 2018, 01:04 PM
Post#8



Posts: 5,614
Joined: 11-November 10
From: SoCal, USA


QUOTE
I think I have the logic on how to query the database to get all the reports and functionality they need; my problem is what is the best way to relate the entities to one another. I know how they're related in theory - just not sure on best practice for creating tables that will lend themselves to all the queries that I need.
...
would there be any gain/loss to making separate tables for each type of transaction altogether?

well, at this point, i'm not seeing a reason to separate transaction types. actually, i'm not seeing why this isn't a fairly simple transaction setup. but then, i've been reading the posts and your Word doc quickly, during breaks at work (i don't have internet at home, at the moment). so it's more than possible that i don't have a clear picture in my head yet.

i'm finding this an interesting business model. if you are interested, and able to hold off until early next week, i'd like to print some of this stuff out and take it home over the weekend, where i can concentrate on it and see what i come up with.

hth
tina
Go to the top of the page
 
MadPiet
post Jan 31 2018, 01:18 PM
Post#9



Posts: 2,602
Joined: 27-February 09



I wouldn't do the "IS-A" stuff unless I really had to.

FWIW, here's what I was told in grad school when a smarty-pants was teaching us... Design your tables. Add at most 3 records to each. test your queries. If they work, proceed. Design more tables. If going that way doesn't meet your needs or you have radically different "classes" of a thing in your database, then address that when you're sure you really need it. Things like that are PITA, don't go there unless you need to.
Go to the top of the page
 
DeborahSV
post Jan 31 2018, 03:18 PM
Post#10



Posts: 103
Joined: 14-September 10



Thank you both for your responses - very helpful.

Tina, I would appreciate your input after the weekend. I would just mention 2 details that I didn't stress in my previous posts: 1. They need deposit reports - and all "transaction types" are mixed together in each deposit. So a deposit would typically include any headchecks (loan payments) whose date has arrived (or passed), fee/penalty payments, donations, fund contributions. 2. They need monthly and yearly reports that show the charity's activities: how many loans/how much money loaned; how much was paid back; how much received in fees/penalties, donations, contributions; how much returned to fundholders; maintenance expenses - and overall totals.
Go to the top of the page
 
tina t
post Feb 7 2018, 01:31 PM
Post#11



Posts: 5,614
Joined: 11-November 10
From: SoCal, USA


Deborah, what happens to the fees that are collected? in looking at the transactions as in-or-out, collected fees would be an "in", but where is the money assigned? is it dumped into the "open" pot of donations? or ear-marked to cover business expenses? or?

hth
tina
Go to the top of the page
 
tina t
post Feb 7 2018, 03:36 PM
Post#12



Posts: 5,614
Joined: 11-November 10
From: SoCal, USA


next question, Deborah: a borrower can make multiple types of payments with one check, correct? such as a single check to cover a loan payment plus the headcheck plus switching the headcheck on a previous payment... or do you require the borrower to write a separate check for each charge?

hth
tina
Go to the top of the page
 
DeborahSV
post Feb 8 2018, 11:14 AM
Post#13



Posts: 103
Joined: 14-September 10



Thanks, Tina.
Great questions.
ques 1: The fees have to be earmarked to cover business expenses. In looking at all the money as "in" or "out", there has to be some connection between incoming money marked "fee" or "penalty" and outgoing money marked "maintenance expense." Now what would happen if expenses would exceed the incoming fees and penalties? Do they dip into the "pot" of donations? I don't know - have to ask them. In their current program, this is how they deal with this: They created one "fund" called "maintenance fund", and they record fees and penalties as deposits into that fund, and they record maintenance expenses as money withdrawn from that fund - i.e. money "returned to the fundholder." It works for them, and perhaps it could work in our scheme as well - because tblTransaction does link to tblFund - but I don't like "pretending" that a maintenance fund is like the other funds - when it's not.
ques 2: I didn't think about that scenario. I assumed each charge is paid for separately - but not a very safe assumption! I couldn't reach the charity today, but I'll post again when I have that answer.
Thank you again for your time and help!
Go to the top of the page
 
DeborahSV
post Feb 8 2018, 11:49 AM
Post#14



Posts: 103
Joined: 14-September 10



Over the weekend, I formulated the requirements for the database more formally (by asking lots of questions) to make sure I understand their needs fully and so that when I do get up to actually designing the tables, forms, and reports, I have a blueprint to follow. I'll upload this doc in case it helps.
Attached File(s)
Attached File  Loan_Database_Requirements.zip ( 16.73K )Number of downloads: 16
 
Go to the top of the page
 
DeborahSV
post Feb 8 2018, 01:47 PM
Post#15



Posts: 103
Joined: 14-September 10



Ok, I reached the secretary at the charity, and she said that it does happen on occasion that one check covers more than one thing - e.g. headcheck + fee, or headcheck toward 2 different loans simultaneously (suppose he took out one loan, and is almost finished paying up, and now took out another loan, he might give new headchecks that work toward both loans together.) What she does now is enters them as separate checks into the database - same check number, and different amounts per entry. So if it's a $525 check, $500 loan payment and $25 fees, she enters a $500 check and a $25 check in the respective places, even though there is physically only one check. When she prints the deposit report, she sees that they have the same check number, so she knows she only has to pull out one check to deposit. She doesn't use the deposit report for the bank; it's only for her, so she doesn't mind that the number of checks listed doesn't match the number of physical checks in hand. She uses Quicken for her bank reconciliation work, not her database. The database is to keep track of the loans and funds from an organizational perspective, not the actual financials.
I guess I didn't think of asking her this question, because when she presented it to me, everything seemed very separate - and now I understand why - because even if it's not separate, she makes it separate.
Does it make sense to continue her modus operandi?
Go to the top of the page
 
tina t
post Feb 8 2018, 02:11 PM
Post#16



Posts: 5,614
Joined: 11-November 10
From: SoCal, USA


Deborah, thanks for the answers and for the additional info doc. i've downloaded it, and will print and read it as soon as i can. it's Thursday already (where did the week go?) so this is going to push me into the coming weekend; sorry i can't devote more time to it. i have built a basic structure of related tables, now i need to review the new info and compare that with my design, and then run some scenarios against it to see how it holds together.

two things you'll want to keep in mind: 1) in Access, data storage and data display/interaction are completely separate, and giving the customer what they want it usually more a matter of how you build the forms and reports than it is of how you store the data, and 2) since the customer has asked for a new and better business solution, they'll have to expect that there may be some changes to how they do things in the application.

hth
tina
Go to the top of the page
 
tina t
post Feb 8 2018, 04:00 PM
Post#17



Posts: 5,614
Joined: 11-November 10
From: SoCal, USA


hello Deborah, i just did a quick read-thru of the Word doc you posted today (2/8). i'm having trouble understanding #2 and #9, i think because i don't really understand what a "headcheck" is. the word always gives me a visual of a psychiatrist... wink.gif

can you help me out here?

hth
tina
Go to the top of the page
 
DeborahSV
post Feb 8 2018, 08:58 PM
Post#18



Posts: 103
Joined: 14-September 10



Tina, it's so nice of you to give of your time to my project.

A "headcheck" is essentially a post-dated check. So if a person takes out a loan for $5000, he can give 10 post-dated checks for $500 each dated the first of each of the following 10 months. It's checks that he gives "ahead" to pay off his loan. The charity requires post-dated checks that total the full amount of the loan to be submitted at the time the loan is given. A feature that they asked for is that the program automatically generate records for these "headchecks." I didn't include this in the original database plan that I posted last week, but I would need another table.
tblLoanPaymentPlan
LoanPaymentPlanID
LPPLoanID
LPPNumberPayments
LPPFrequency - this will be a combobox with values Monthly, Twice Monthly, Every Other Month
LPPAmount
LPPFirstCheckNumber
LPPFirstDate
In the above example, we'd enter 10 for number of payments, monthly for frequency, 500 for amount, 301 for check number, and 3/1/18 for first date. Then, pressing a button would run an append query (or execute some code) that would enter 10 checks, beginning with check number 301 (we assume people give consecutive checks; if not, it can be modified afterwards) on March 1, check 302 on April 1, etc.
But sometimes, people don't follow one pattern for all their checks. Suppose the borrower wants to start by paying only $200 a month for the first 10 months, but after that will raise it to $300 for the next 10 months. So this loan would have 2 associated records in tblLoanPaymentPlan.
So much for "headchecks."
Now maybe somewhere along the line the borrower gets a windfall and wants to pay up a large portion of the loan. So he comes with a new check for $2500. That check can also be entered into the table. (and some "headchecks" would be voided.) From a development point of view, it makes no difference for us whether it's a headcheck (given ahead) or a new check that he brought today. They are both "loan payments" in the transaction table. But in their existing program, they were never able to modify anything in the "headchecks" table and other checks had to be entered in a different (convoluted) way. So I wrote it that way in the requirements document, because that's how she was used to thinking about it. But really both items will be seen in the same subform in the loan form.

Go to the top of the page
 
tina t
post Feb 20 2018, 04:18 PM
Post#19



Posts: 5,614
Joined: 11-November 10
From: SoCal, USA


hello Deborah, i'll have a sample db in A2003 to upload tomorrow. i figured it would be easier than trying to lay it out in a post. you should be able to open an A2003 db in newer versions of Access, though you may have to convert it to your version if you want to play with it (make design changes).

it's always dicey setting up detailed tables/relationships without a detailed process analysis, which obviously i couldn't perform. so i went off the info you provided, and there may be aspects of the business process that are not included. take it for what it's worth; it probably won't meet all your needs, but at least provides the bare bones of an approach that may work for your business model.

just a reminder: data storage and data presentation/interaction are completely separate in Access. making this database as user friendly as possible will require a fair amount of coding to help the user work accurately and efficiently.

and sorry i took so long. if it turns out you've already moved on, and the sample db isn't useful to you, that's okay. it may help somebody else, down the line.

hth
tina
Go to the top of the page
 
DeborahSV
post Feb 21 2018, 09:50 AM
Post#20



Posts: 103
Joined: 14-September 10



Thanks so much!
I was busy with other things so didn't yet move on much with this project, and I'm looking forward...
Go to the top of the page
 
3 Pages V  1 2 3 >


Custom Search


RSSSearch   Top   Lo-Fi    14th December 2018 - 12:50 AM