Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Posts
    2

    Designing Database Tables

    Hi,

    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.

    AccountConfiguation Table:

    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.

    AccountDetails Table

    AccountID Column Id Data
    ========= ========= =====

    Please help me to identify the best method or is there any other way.


    Best Regards,
    Chamal.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    You are in danger of straying into the big mistake that is Entity-Attribute-Value (EAV). You don't want to go there.

    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.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do a search for subtype/supertype
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •