CODEWITHSUNDEEP
sqloracle-database
bbb
bbb

Reputation: 1489

Oracle SQL: Foreign key from three possible tables?

Take the following scenario:

CREATE TABLE customers (
  cust_num INTEGER PRIMARY KEY,
  cust_name VARCHAR(60) NOT NULL,
  //other info
);

CREATE TABLE checking_account (
  acc_num NUMBER(16) NOT NULL,
  acc_type VARCHAR(8) NOT NULL,
  //other info
);

CREATE TABLE savings_account (
  acc_num NUMBER(16) NOT NULL,
  acc_type VARCHAR(8) NOT NULL,
  //other info
);

CREATE TABLE loan_account (
  acc_num NUMBER(16) NOT NULL,
  acc_type VARCHAR(8) NOT NULL,
  //other info
);

CREATE TABLE has_account (
  acc_num NUMBER(16) NOT NULL,
  acc_type VARCHAR(8) NOT NULL,
  cust_num INTEGER
);

More than one customer may have the same account and additionally, one customer may have multiple accounts. The account number in the has_account table may or may not be unique across accounts.

How could this be represented? I have tried to implement class table inheritance and concrete inheritance but I can't figure out how to allow one account number to be shared across multiple accounts. I have yet to find an example or explanation which makes this consideration. Could anybody give me an insight as to how to achieve this functionality or at least point me in the right direction? Any help is greatly appreciated

Upvotes: 1

Views: 86

Answers (3)

Muhammad Muazzam
Muhammad Muazzam

Reputation: 2800

Create custReg table which have parent-child relationship of account types between column data. That column named accountID would be PK. As common attributes can easily be placed in single table.

Further tables with different attributes can be created and subsequently linked with theirs account ID at first-child level.

Then use hierarchical queries to access data between tables.

Upvotes: 0

D Stanley
D Stanley

Reputation: 152566

Not a complete answer and too long for a comment but I thought I'd address some of your reasons why you have three separate tables:

"checking account doesn't have an interest rate"

This is a business rule and should not be implemented by a different table structure. Also, in times of higher interest rates it's certainly plausible for a checking account to earn interest. Business rules are usually much easier to change that data structures.

a loan account doesn't have a balance

Again, this is a business rule - but certainly a loan has a principle balance.

one account number may be shared across multiple account types ... account number would need to be a primary key in which case it couldn't be shared across accounts

One way to solve that is to use account number , account type as a "logical" compound primary key (note that in most DB systems there are benefits to using a sequence number as a "true" primary key that is independent of the actual record information. What if an account number changes for some reason?

If there are attributes of one account type that cannot feasibly stored in a "shared" data model, then you could model those as sub-tables:

                       |- 0:1 -- 0:1 CheckingAccount    
Customer 1--* Account -|- 0:1 -- 0:1 SavingsAccount
                       |- 0:1 -- 0:1 LoanAccount

But you may find that you end up with similar problem that are more easily solved using business rules that separate data structures.

Upvotes: 0

Amandeep Singh Bhatia
Amandeep Singh Bhatia

Reputation: 131

'customers' table is your primary table which should be linked with all 3 tables 'checking_account','savings_account' and 'loan_account'.In these 3 table there should be one column cust_num which will represent forign key.

So if customer has saving account and loan account then for this customer there is 2 row in customers table and one-one row in savings_account & loan_account table.

Customer all account info should be in has_account table where cust_num is forign key so you can easily find customer info with his account details via join on customer & has_account table.

If you want to know one customer has how many account then use count(cust_num) in your customers table.

Note - If you follow good DB design then you should have only one table called as 'cust_account' in which columns should be like acc_num,acc_code,acc_name etc and acc_type column should be updated with valid value like 'saving','loan' or 'checking'. In your table structure acc_type column is given for all 3 account type tables which has no sense if you have different table for different account type.Remove this column if you are going to use seprate table for account type otherwise use one table with column acc_type.

Upvotes: 1

Related Questions