Solved

Relating one field to many

Posted on 2011-09-06
29
218 Views
Last Modified: 2012-05-12
Experts,
I have several related tables.
tblCustomerData has a field for Bank
tblBank has BankName and AccountType
tblBankAccountType has the type of accounts

A customer can have many Bank Accounts with different type of accounts

What's the best way to tie tblCustomerData, Bank to the various different bank accounts a customer can have such that I do not need a separtae field in tblCustomerData for each type of possible account?
0
Comment
Question by:Frank Freese
  • 11
  • 8
  • 8
  • +1
29 Comments
 
LVL 8

Expert Comment

by:stalhw
ID: 36490216
Create a table
tblCustomerBankAccount
with fields:
id of customer
and id of bank
and id of bankaccounttype
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36490219
Here is how I see it, in a general sense:

tblCustomer
cID (pK)
cFN
cLN

tblBank
bID (PK)
bName

tblAccounts
aNo (PK)
aType
a_bID (FK)

tblCustAcct
caID (PK)
ca_cID (FK)
ca_ANo (FK)

JeffCoachman
0
 
LVL 4

Expert Comment

by:yesthatbob
ID: 36490278
I may be reading this wrong, but it seems your Bank table is really a BankAccounts table. I would have structured your tables like this:

tblBanks: Defines the various banks, contains bank data only
tblBankAccountTypes: Defines the available type of accounts
tblCustomerData: Defines each customer (independent of the bank data)
tblBankAccounts: A many-to-many-to-many table that links banks to the account type, and to the customer who "owns" that account

See the attached ERD for more info. In this scenario, you define the banks, the account types, and the customer independently. In the linking table you are saying, "This customer (John Doe) has theis type of account (Chekcing) at this bank (Chase)".

Table structure example
0
 

Author Comment

by:Frank Freese
ID: 36490429
yesthatbob,
let me make sure I understand. In my tblCustomerData I do not need a field for Bank. If a customer has many different bank accunts and associated banks then to maintain data integrity I would make BankAccountTypeID, CustomerID and BankID a unique index?
0
 
LVL 4

Accepted Solution

by:
yesthatbob earned 500 total points
ID: 36490496
Yes, each iof those fields should be the primary key on their respective tables.
0
 

Author Closing Comment

by:Frank Freese
ID: 36490509
thanks
0
 
LVL 4

Expert Comment

by:yesthatbob
ID: 36490540
I've updated the primary key column names in the diagram to hopefully make it easier to understand.
 Table structure example
0
 
LVL 8

Expert Comment

by:stalhw
ID: 36490580
Ok, I guess my answer wasn't clear enough, but was exactly the same thing...
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36490587
fh_freese,

I saw it a slightly different way.

Can I ask why you never responded to my post?
0
 

Author Comment

by:Frank Freese
ID: 36490730
sure,
i did not see how you tied back to the customer
0
 

Author Comment

by:Frank Freese
ID: 36490736
also, the visual representaion helped a lot - nothing personal for I appreciate all experts input
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36490891
The two approaches are similar, but not the same.

You did not clearly define your criteria for doing this so one design might work certain instances were the other might not.

In my design, one customer is tied to the Account (number).
(The account type is simply "Looked up")
Hence, One Customer can have many accounts

In yesthatbob's, design the customer is associated directly with the account type.

But as I see it, the customer is tied to the Account directly.
(For example an account number is assigned to a customer)

The account Type is only tied to the customer inderctly through the Account
(ex." you do not really assign Account "Types" directly to a customer....)

JeffCoachman
0
 

Author Comment

by:Frank Freese
ID: 36490909
I apologize for being unclear - I assure to you it is not by design. thanks for your feedback and I'll continue to work at clarify my questions.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36490922
So are you saying that you associate customers *directly* with Account types or Account numbers?
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:Frank Freese
ID: 36491655
One customer can have many different types of banking accounts, savings, checking, line of credit, none, etc.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36491766
But they can have many accounts as well...

In your case what is the theory that says it is better to link the Cust to the AcctType, rather than the account?.

Just to be clear, I am not in any way saying that yesthatbob's design is incorrect, only that you as the developer here must be fairly good in Table design theory to say what the best design will be in all situations.

Finally, note your own post:
<A customer can have many Bank Accounts with different type of accounts>

Notice here that you actually note the Cust/Account relationship first, and the acct type is secondary, (as I stated, it can be "Looked" up)

So again, I just need to know the theory behind linking to the accttype rather than the Accountnumber...

JeffCoachman
0
 
LVL 4

Expert Comment

by:yesthatbob
ID: 36491979
FWIW, I think Jeff's (boag's) design is better. Doing it the way he recommends allows you to know what banks offer what account types before assigning a customer to them. In my design, you can't have an account offered by a bank without actually having a customer who owns that type of account. Bad form; Jeff corrects that. Here's my ERD updated to match Jeff's design to hep[ you get what he's saying:

 Table structure example
0
 

Author Comment

by:Frank Freese
ID: 36492154
wow! this is a great thread for me. I have a few questions though. For example, in tblBank, would I make the BankName a unique index (the same for BankAccountType for AccountType)? In the tblAvailableAccounts would I set a unique index on BankID and BankAccountTypeID? Finally, on tblCustomerAccounts do I need the keyed field CustomerAccountID or can I have CustomerID serve as my key field?
0
 
LVL 4

Expert Comment

by:yesthatbob
ID: 36492203
I would think it's a reasonable expectation that the bank name would be unique. Certainly account type, which would be a lookup table, the name would want to be unique.

You are also correct on the unique index on the BankID/BankAccountTypeID combo.

On the CustomerAccountID table, you'll need a surrogate key (CustomerAccountID) because you have a one-to-many relationship between Customers and CustomerAccounts, as well as a one-to-many relationship between AvailableAccounts and CustomerAccounts. THis table simply serves as many-to-many link between Customers and AvailableAccounts. In other words, one customer may have many different accounts, so you can't use CustomerID as the primary key in the CustomerAccounts table.
0
 

Author Comment

by:Frank Freese
ID: 36492234
you all just educated me today....now comes the problem. Do you have a problem splitting the 500 points between yourself and boag2000? I don't know how to do that but both have been very helpful.
0
 

Author Comment

by:Frank Freese
ID: 36492250
I have another question. Where does the account number go, such as checking account number or savings account number?
0
 

Author Comment

by:Frank Freese
ID: 36492260
Would it go in the table tblCustomerAccounts as another field?
0
 
LVL 4

Expert Comment

by:yesthatbob
ID: 36492460
Yes. It is an attribute of a specific account owned by a customer.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36492642
yesthatbob,

Just to be clear, I was not in any way suggesting that your first approach was not valid.

I just wanted to know how fh_freese arrived at the conclusion.

Any design can "make sense" when you first look at it.
But once you actually put it into practice, it may become clear that further refinements may be needed.


I can see by your posts and the time you invested in the solution, then you know the rules of table design.
I just wanted to make sure that fh_freese knew that all we can do is make "suggestions" based on how we see the issue.

Again, I was not seeking any points just clarification...

;-)

Jeff
0
 
LVL 4

Expert Comment

by:yesthatbob
ID: 36492660
Jeff,

No offense taken at all. Your approach was the right one on the point you made, and I'm too old and done this too long to not recognize it when that is the case. I would encourage fh_freese to throw you an assist on this one, if it's not too late.

What I do see as important was the way we collaborated on it to help fh_freese on this. That's what make this line of work fun IMHO.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36492668
fh_freese,

As I posted above (and I am sure you know from my other posts in your threads), I am not seeking any points, only clarification.

AFAIC you can leave all the points to yesthatbob, as he seems to have a solid grip on the concepts involved in this issue.

;-)

Jeff
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36492779
And <I'm too old and done this too long to> know that there will always be another question to make up for the points.
Besides, I have enough, and you deserve them here.

Besides, I've made a new friend, correct?

;-)

Jeff
0
 
LVL 4

Expert Comment

by:yesthatbob
ID: 36492792
@Jeff:

Of course!
0
 

Author Comment

by:Frank Freese
ID: 36492941
thanks guys - this truly helped me. I'll be bacck
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now