[lmei007]

Have a question on how to connect tables. if I have a CUSTOMER table (with custid as primary key) and a BANK_ACCOUNT table (with accountid as primary key). To connect them together, we have at least two options:

option 1:
put accountid into CUSTOMER table as a foreign key

option 2:
put custid into BANK_ACCOUNT table as a foreign key

to me option 2 may be better because it can handle one customer has more than one account case. But how about if that account is a shared account which shared by a hushand and wife, and the husband and wife are separated customer in this system? Then we need another table in between to resolve this?

What's your opinon? do we have general rules for those cases?

[georgev]

You're asking the right questions, but your asking the wrong people - we don't know what your business requirements are.

[blindman]

When an account can be shared, you need to implement a many-to-many relationship. This is done by creating a third table that contains AccountID and CustomerID as a unique composite key.

[mike_bike_kite]

Just have a separate CUSTOMER_BANK_ACCOUNT table surely.

CUSTOMER_BANK_ACCOUNT : custid, accountid, role

With role to show who's the primary owner of the account. This handles 1:M, M:1 or M:M between customers and accounts.

Edit : Sorry blindman - didn't see your reply until too late.