Let's assume we have to develop an application for a bank.
There are different types of Accounts in a Bank. ex: Fixed Deposit Accounts, Current Accounts, Savings Accounts
There is also the possibility of adding new account types.
How should we design the database.
Have one Table to store common account data and three more tables to store data of 3 account types?
But I will have to create a new table and amend my code to access new table if I add another type of account.
Another method I thought of is to use a table to store configuration data.
Account Type Column Id Column Desc
=========== ====== =========
And add column details for each table.
Then have one main table to save details of all 3 account types.
AccountID Column Id Data
========= ========= =====
Please help me to identify the best method or is there any other way.
Yes, you'd have to amend your code if you introduced a new account type. Surely you'd have to do that anyway if it had new attributes?
If there aren't too many account types or too many columns altogether, you could simplify to a single table with all common and type-specific columns. The type-specific columns would be NULL for other types and check constraints would be used to ensure the appropriate columns were used for the account type.