Relating one field to many

Create a table
tblCustomerBankAccount
with fields:
id of customer
and id of bank
and id of bankaccounttype

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

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:


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)".

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?

thanks

I've updated the primary key column names in the diagram to hopefully make it easier to understand.
 

Ok, I guess my answer wasn't clear enough, but was exactly the same thing...

fh_freese,

I saw it a slightly different way.

Can I ask why you never responded to my post?

sure,
i did not see how you tied back to the customer

also, the visual representaion helped a lot - nothing personal for I appreciate all experts input

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

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.

So are you saying that you associate customers *directly* with Account types or Account numbers?

One customer can have many different types of banking accounts, savings, checking, line of credit, none, etc.

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

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:

 

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?

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.

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.

I have another question. Where does the account number go, such as checking account number or savings account number?

Would it go in the table tblCustomerAccounts as another field?

Yes. It is an attribute of a specific account owned by a customer.

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

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.

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

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

@Jeff:

Of course!

thanks guys - this truly helped me. I'll be bacck