SQL Server Performance

Database Design Question: Too many lookupTables?

Discussion in 'ALL SQL SERVER QUESTIONS' started by Brandon Sharbo, Dec 2, 2013.

  1. Brandon Sharbo New Member

    Hello everyone. My company has a query that is run on a very large fact table. The table has Approximately 900 columns of distinct data, of which about 700 is attribute data. (It is loan data with borrower information).

    The issue is some common fields within the database need to be represented differently for Each client in reporting that needs to be done. For example, one client might want FHA loans to display as FHA, another as FHA Loans, a third as "FHA Processed" etc.. (this is just an example).

    Generally I would do this in a dimention table with one column per client, and then dynamically select the appropriate column per client. However this needs to be done on almost every attribute within the fact table (~700), and 700 dimention tables just doesn't seem efficient.

    The other issue is a 700 column case when statement where each statement is multiple lines long takes a very long time to update and is prone to user error.

    Has anyone run into a problem similar to this before and what methods have they used to solve it?
    Thanks!
  2. davidfarr Member

    Could you consider a design similar to this;
    Code:
    tblBankClient;
    BankClientID BankClientName
    1            NedBank
    2            Crown Bank
    3            Bank of Athens
    
    tblColumnType
    ColumnTypeID ColumnTypeName
    1            FHA Loans
    2            Home Loans
    
    tblClientColumns
    ClientColumnID BankClientID ColumnTypeID NamingConvention
    1                1            1              FHA
    2                2            1              FHA Loans
    3                3            1              FHA Processed
    4                1            2              Home
    5                2            2              Home Loans
    6                3            2              Home Loans Processed
    
    The idea of the above is that you would not need to add multiple columns for every new Client or every new Report column type that you add. You could dynamically build the report, or even dynamically build the T-SQL query for the report by using the structures above to obtain the correct column name per client and per column type.

Share This Page