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    
 
   
tina t
post Feb 21 2018, 04:27 PM
Post#21



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


okay, Deborah, here it is. i've attached the zipped .mdb file, and a pdf that shows the table relationships; there's also a report object in the db that shows the same thing. hopefully you'll be able to open the tables in Design view, either as is or after converting to A2007. i added some notes in Design view to document purpose, origin of foreign key fields, etc. the major changes from your design are:

1. a tblLoanDetails, as a child table to tblLoans. this table holds the details of headchecks, fees, and penalties. since fees and penalties are tracked here, there are no separate tables for those items. i believe this is a more normalized structure, as the other method stores data in table names: tblLoanFees and tblPenalties (that's not quite what you named them, but the issue is the same). you also won't need a table to store records of the headchecks' "setup". instead, you'd use unbound controls in a form to provide the base info to append headcheck records to tblLoanDetails - one record for each physical headcheck.

2. a tblTransactionDetails, as a child table to tblTransactions. this table holds the details of where the money from each check will be applied, or "dispersed".

hth
tina

Attached File  CharityLoans.zip ( 24.44K )Number of downloads: 9

Attached File  CharityLoansRelationships.pdf ( 30.95K )Number of downloads: 5
Go to the top of the page
 
DeborahSV
post Feb 26 2018, 01:17 PM
Post#22



Posts: 103
Joined: 14-September 10



Thank you for all of your time. So very, very kind of you.

I like the addition of tblTransDetails as it allows the "dispersal" of a single check to different purposes. I understand it requires coding in the UI, but definitely worthwhile (and necessary.) Thanks.

However, I am still stuck with my original question. How do I "disperse" different types of transactions? In your tblTranDetails there is a FK to tblTransCategories - and the data in that table includes fund contributions and loan payments (and many others.) tblTransDetails has a FK to tblLoanDetails, (or as you wrote, we could make that join to tblLoan) so I could disperse a check to 2 different loans, but it has no FK to the funds table. I need to "disperse" a transaction whose category is fund contribution to (a) particular fund(s). This was how I began this thread. I wanted to know if it's OK to have a few different FK's in a table, wherein some will necessarily remain null for each record. (i.e. This transaction is either linking to a fund or a loan - not both - because its category is fund contribution or loan payment [mutually exclusively].) (In your design, tblTransactions is joined to tblAccounts, but for fund contribution, I need to connect it to the fund, not the person, because one person can have more than one fund. A donation would connect directly to a person, but a fund contribution to a fund. So between loan payment, fund contribution, and donation (3 of the transaction categories), I need 3 FK's of which 2 will be blank for every record.)

I was also wondering about your tblLoanDetails and its accompanying tblPaymentCategories. It seems to me a repeat of tblTransactions and tblTransCategories?

But my main question is the original one. MadPiet wrote earlier in the thread that this is called "IS-A hierarchy", but implied that I need not use that structure if I have a structure that works. The structure that I think would work is having various FK's of which some stay blank. Does this make sense?
Go to the top of the page
 
zaxbat
post Feb 26 2018, 02:07 PM
Post#23



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


Looks nice, Tina. One thing I see that is worth considering... for my contact entities they all go into one table (same as you have done). That just makes a ton of sense so you don't have to update multiple addresses in multiple tables when somebody moves. But your AccTypes field in the Accounts table only allows a contact entity the ability to be one type (unless you plan to utilize multi-select)...so if somebody who contributed money one day needed a loan....it might not be reflected properly in the Accounts table. Sorry to be so verbose, and this is just a suggestion...one thing that I found helps a lot is a yes/no column in the accounts table for each AccType. Then you can toggle the appropriate ones on or off. Is it necessary? No, but it is inexpensive on data space and screen space and really makes it easier for so many operations that you will be performing within the DB. If you already have this covered somehow...then, sorry for sticking my nose in. Cheers!
This post has been edited by zaxbat: Feb 26 2018, 02:09 PM
Go to the top of the page
 
tina t
post Feb 26 2018, 04:44 PM
Post#24



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


QUOTE
.one thing that I found helps a lot is a yes/no column in the accounts table for each AccType. Then you can toggle the appropriate ones on or off. Is it necessary? No, but it is inexpensive on data space and screen space and really makes it easier for so many operations that you will be performing within the DB.

that design breaks the rules of normalization by storing data in fieldnames, rather than in fields. it also limits expandability of the db; if a new type is added, the developer must change the structure of the table, and change queries, forms, reports, and code, to support the changed structure. a table-based solution would be much better. if one account type may apply to many accounts, and one account may belong to many types - at the same time, or over time - then that is a many-to-many relationship that is easily and properly handled by creating a join or junction table between tblAccounts and tblAccTypes to support the relationship between the two.

hth
tina
Go to the top of the page
 
tina t
post Feb 26 2018, 04:52 PM
Post#25



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


QUOTE
I was also wondering about your tblLoanDetails and its accompanying tblPaymentCategories. It seems to me a repeat of tblTransactions and tblTransCategories?

no, not repeating table. tblLoanDetails keeps track of the post-dated checks that are given to the charity, and the fees and penalties that are applied to an account's particular loan. tblTransactionDetails are the details of the actual money coming in and going out.

QUOTE
However, I am still stuck with my original question. How do I "disperse" different types of transactions? In your tblTranDetails there is a FK to tblTransCategories - and the data in that table includes fund contributions and loan payments (and many others.) tblTransDetails has a FK to tblLoanDetails, (or as you wrote, we could make that join to tblLoan) so I could disperse a check to 2 different loans, but it has no FK to the funds table. I need to "disperse" a transaction whose category is fund contribution to (a) particular fund(s). This was how I began this thread. I wanted to know if it's OK to have a few different FK's in a table, wherein some will necessarily remain null for each record. (i.e. This transaction is either linking to a fund or a loan - not both - because its category is fund contribution or loan payment [mutually exclusively].)

fair enough, hon, and sorry - this must be a letdown and frustration to you. i don't have A2003 here at work, so i can't review my design. i'll have to look at it this evening, and think about it - right now i don't remember if i had it figured out and didn't document it well, or if i really screwed up and overlooked that whole point.

hth
tina
Go to the top of the page
 
tina t
post Feb 28 2018, 02:00 PM
Post#26



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


QUOTE
However, I am still stuck with my original question. How do I "disperse" different types of transactions? In your tblTranDetails there is a FK to tblTransCategories - and the data in that table includes fund contributions and loan payments (and many others.) tblTransDetails has a FK to tblLoanDetails, (or as you wrote, we could make that join to tblLoan) so I could disperse a check to 2 different loans, but it has no FK to the funds table. I need to "disperse" a transaction whose category is fund contribution to (a) particular fund(s). This was how I began this thread. I wanted to know if it's OK to have a few different FK's in a table, wherein some will necessarily remain null for each record. (i.e. This transaction is either linking to a fund or a loan - not both - because its category is fund contribution or loan payment [mutually exclusively].)

okay, Deborah, i've attached a revised demo. i added the tblFunds' PK as a foreign key in tblLoanDetails. but i have to say, i'm not happy with this solution. it doesn't seem right to me. it really feels like the design is fundamentally flawed, but i can't figure out how else to set it up.

i originally took an interest in this business model because i wanted to correct the splitting of loan charges into two different tables - fees and penalties - which was storing data in table names. now it feels like my design is doing the same thing - storing a single entity into two examples of that entity: funds and loans - storing data in table names. but what that entity is, i can't figure out. i've tried to come at it from different angles, but i'm missing something.

i've contacted one of the VIPs, and asked him to put a call out to all the VIPs, asking for anyone with Accounting db experience to take a look and offer a better solution. i know you said the charity is using Quicken (?) for the actual bookkeeping, but this still feels like an Accounting-based issue to me.

hth
tina

Attached File  CharityLoans.zip ( 25.02K )Number of downloads: 4

Attached File  CharityLoansRelationships.pdf ( 31.43K )Number of downloads: 9
Go to the top of the page
 
projecttoday
post Mar 1 2018, 07:56 AM
Post#27


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


Am I correct in that you can receive a check for $100 but the payer of that check could want $68 of it applied to one loan and $32 applied to another loan? Or applied to one fund and another fund? Or applied to one account and another account? Or any of the above?

So are loan recipients paying down loans or accounts? What kind of statement do you currently send out to people, say in the case where a person has >1 loan? Do you send 1 statement per person or 1 statement for each loan?
Go to the top of the page
 
DeborahSV
post Mar 5 2018, 01:08 PM
Post#28



Posts: 103
Joined: 14-September 10



Thank you both for your responses.
It's complicated; I hope I can convey it clearly.

QUOTE
Am I correct in that you can receive a check for $100 but the payer of that check could want $68 of it applied to one loan and $32 applied to another loan? Or applied to one fund and another fund? Or applied to one account and another account?

The answer to questions 1 and 2 is yes; question 3 is no.

An "account" in this database essentially means a "person". A "person" can be a "borrower", which means that he took out a loan, or a "fundholder", which means that he temporarily donated funds that the charity can circulate in loans, but he will one day withdraw them.
(BTW a "person" can be other things in this database too, like a "consigner" or a "donor", but for the purposes of my question we can restrict "person" to one of the two mentioned above.)
(One person can be both a borrower and fundholder at different times, but that doesn't concern us here. In any given transaction, he will only be one or the other.)

So there is money coming into and out of the charity, and it has to be recorded and dispersed to the right places. If someone gives a check to add to his fund (i.e. "person" is a "fundholder" now), then the transaction detail table has to have a join to the fund table. If someone gives a check to pay off his loan (i.e. "person" is a "borrower" here), then the transaction detail table has to have a join to the loan table. One check may be dispersed to more than one loan at a time, just as one check may be dispersed to more than one fund at a time, but a check would never be dispersed to both a fund and a loan simultaneously. The question is: is it OK database design to put 2 different foreign keys in the transaction detail table (TransactionDetailFundID and TransactionDetailLoanID), wherein some transactions are loan payments and some transactions are fund contributions, such that one of the two foreign keys would necessarily be blank for each record? This question stands even if one check would not be dispersed over multiple loans or funds. The very fact that there are different types of transactions stored in the same table necessitates the ability to join some records to one table and some records to another table.

I tried to articulate this question in my very first post in this thread in the paragraph beginning "It would seem wise to me..." I also expressed the question in the DatabasePlan doc that I uploaded in my second post in the thread in the paragraph beginning "This is where I'm stuck." The design has been modified somewhat (thanks to Tina!) since my descriptions in those posts (especially relevant here is the addition of the transactionDetails table to "disperse" a transaction), but the original question remains.

QUOTE
So are loan recipients paying down loans or accounts? What kind of statement do you currently send out to people, say in the case where a person has >1 loan? Do you send 1 statement per person or 1 statement for each loan?

1. loans, not accounts
2. We don't have statements because the charity requires every borrower to present a series of post-dated checks (they call them headchecks) that total the full loan amount at the time the loan is given. So there is no need for statements. A scenario could be created where a person has one check being applied to more than one loan if the borrower takes out a second loan before he finished paying off his first loan (i.e. before all of those post-dated checks reached their dates), and he wrote a new set of headchecks for the second loan that incorporated the outstanding balance of his first loan into the total (and then voided the remaining unused post-dated checks from the first loan).

I hope I have been clear.

Thank you very much.
Go to the top of the page
 
tina t
post Mar 5 2018, 01:30 PM
Post#29



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


QUOTE
The question is: is it OK database design to put 2 different foreign keys in the transaction detail table (TransactionDetailFundID and TransactionDetailLoanID), wherein some transactions are loan payments and some transactions are fund contributions, such that one of the two foreign keys would necessarily be blank for each record? This question stands even if one check would not be dispersed over multiple loans or funds. The very fact that there are different types of transactions stored in the same table necessitates the ability to join some records to one table and some records to another table.

hi Deborah, my response to the question (though you had to knock me on the head to get it) was to go ahead and add both foreign key fields to tblTransactionDetails. i did also say that i'm not too happy with the solution, but still have not come up with something better. i was hoping for one or more VIPs with Accounting db experience to weigh in and put us right, but that hasn't happened.

suggest you add some dummy data to my demo, or to your modified db, and see if you can query out the results you're going to need.

hth
tina
Go to the top of the page
 
projecttoday
post Mar 5 2018, 04:51 PM
Post#30


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


You need a checks table:
tblChecks
CheckID
checknumber - numeric
accountID
note - text
date_
amount -currency

There should be a unique index on the combination of checknumber, accountid, and note. In the transactions table there is an amount (not check amount). On the transactions form there is a combo box for the checks (filtered by accountid). Select a check, then enter the amount you want to take off the check. The form's code checks the amount to see if there's that much left on the check. If the check isn't in the combo box, you have to enter the check. There is a button which pops up a form for entering checks. In the rare instance when an account holder gives you 2 checks with the same number, enter them both putting something in the notes field.

The transactions table needs a single transaction types field (id to the transactions types table which would have 2 records (at least), payment and contribution, and these could have flags indicating + or - (debit or credit) or you could make a table for that. The transactions table then would also have fund id and account id fields which would be entered depending on the transaction type or left blank.
Go to the top of the page
 
DeborahSV
post Mar 6 2018, 12:56 PM
Post#31



Posts: 103
Joined: 14-September 10



Thanks Tina and Robert.
So it seems at the end of the day all agree that the best idea is to have a few foreign keys in the transactions table, and some will be blank for each record.

Now about this checks table: Is this to replace tblTransactionDetails? i.e. Record the actual check in tblChecks, and then "disperse" it in tblTransactions? I like this idea, because this way my transaction table actually has the transactions, not the full check amount, and it has a FK to the actual check. Am I getting it right?
Go to the top of the page
 
projecttoday
post Mar 6 2018, 03:19 PM
Post#32


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


Yes, but the transaction type id should always have a value.

Yes.
Go to the top of the page
 
DeborahSV
post Mar 6 2018, 06:55 PM
Post#33



Posts: 103
Joined: 14-September 10



Great. Many thanks.
Go to the top of the page
 
projecttoday
post Mar 6 2018, 07:26 PM
Post#34


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


yw.gif
Go to the top of the page
 
DeborahSV
post Mar 26 2018, 09:44 AM
Post#35



Posts: 103
Joined: 14-September 10



Hi again,
After a little break, I'm back at this project and designed my tables, but I still have some questions about tblCheck and tblTransaction and how they work with one another.

1. TblTransaction includes all transaction types - debits and credits. Should tblCheck also include both + and -? The reason we need tblCheck is that sometimes a person gives one check that has to be dispersed over various transactions, so obviously it is needed for the credits. But for the debits, meaning the checks that the charity writes out, these are always a single check for a single purpose - either a loan, or a fund return, or a maintenance expense. Should the checks being written by the charity also be recorded in tblCheck?

2. When someone asks to hold (i.e. delay depositing) his check or void a check, should this be a field in tblCheck or tblTransaction? [In the UI (that I'm envisioning, I haven't actually begun any forms until I get the tables right), the user generally interacts with a form based on tblTransaction, and from there, he could click on a button to see/enter the check for that transaction (tblCheck). Perhaps there should also be the option of viewing all his checks, wherein he could drill down and see how each check was applied?] I'm guessing that these fields should be in tblCheck - because truly holding/voiding a check applies to the check as a whole, not just one transaction, but I want to be sure I'm understanding this correctly. Never worked with this kind of model before.

3. What is the best way to handle deposits? Should tblCheck have a DepositDate field, or is it better to have a separate tblDeposit, and then a many-to-many junction table that joins tblDeposit and tblCheck. I should add that sometimes they do more than one deposit in one day, so I would really need DepositDate and DepositBatch.

Thank you so much in advance! The table design and relationships of this database have already come a long way since I first began thanks to the good folks in this forum!
Go to the top of the page
 
projecttoday
post Mar 26 2018, 03:03 PM
Post#36


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


1. Aren't your organization's accounts receivable and accounts payable functions distinct from one another? I would keep them separate. There was a recent thread discussing this.
2. Checks should be managed by the check management table(s).
3. Not sure what you mean. A deposit and a check are 2 different things, right?
Go to the top of the page
 
DeborahSV
post Mar 27 2018, 11:50 AM
Post#37



Posts: 103
Joined: 14-September 10



Thanks for the reply.

1-

I think I found the thread, which advised separate tables for AR and AP. Mine is not a typical accounting db, so I want to be sure it still makes sense to have separate tables.

The database mainly records loans given from (AP) and paid back to (AR) a charity, with the accompanying fees (AR), and it also records funds "lent" to the charity (AR) and then returned back to the fundholders (AP), as well as general donations (AR) and expenses (AP). If I use separate AR and AP tables for the checks themselves, then should I still have one transaction table that records how the check was applied?

Also, in the AR area, often a borrower will give one check that applies to various transaction types, which creates the need to "disperse"/"apply" the check, but AP has no such need, since every check is written for one thing. So a tblCheckPayable can really have a field TransactionCategory in it, whereas tblCheckReceivable needs to join to a Transaction table to "apply" the check. If I make two separate check tables, should they be consistent in this regard?

I'm an Access amateur who really enjoys designing user-friendly forms and reports, and I think I have a fair picture of how things should "look" in the end, but I don't want to jump into the FE of this project until I work out the BE entirely, which is proving more difficult than I expected!

3 - A deposit is essentially a list of checks. (i.e. The secretary has many physical post-dated checks, which have been recorded in the database, and when their date arrives, she deposits them in the bank, and needs to record in the database that they've been deposited.) Does that clarify my question?

Thank you.
Go to the top of the page
 
projecttoday
post Mar 27 2018, 02:37 PM
Post#38


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


1. I'm glad you read that thread. As I said, I believe that everything including checks should go in separate tables for AR and AP.
3. What do you need the deposits table for? Do you need it if you're recording payments in the transactions table and all payments are checks?
Go to the top of the page
 
DeborahSV
post Mar 27 2018, 09:40 PM
Post#39



Posts: 103
Joined: 14-September 10



Thank you.

We need a record in the database of what date each check was deposited in the bank. So I was wondering if I should have a deposits table, and then a junction table joining deposits and checks, or whether I should just add deposit date field to the checks table.
Go to the top of the page
 
projecttoday
post Mar 27 2018, 10:58 PM
Post#40


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


Oh, you're depositing the checks into your bank. Then you would be doing cash management of your bank account(s). In that case you would put all deposits into tblDeposits not just the loan payment checks. That would be a situation where you would want a deposits table. On the other hand, if you're not doing cash management, can you think of any actual use for a separate deposits table?

You could add it later.
Go to the top of the page
 
3 Pages V < 1 2 3 >


Custom Search


RSSSearch   Top   Lo-Fi    11th December 2018 - 01:44 AM