SQL Server Performance

SQL with inheritance and over-rides

Discussion in 'ALL SQL SERVER QUESTIONS' started by Questech, Mar 10, 2013.

  1. Questech New Member

    I am looking for the best way to represent OOP concept of inheritance along with the capability of over-riding some values in inherited table. I have looked at class table inheritance and similar ideas but none of them seem to replicate the OOP concept to override some of the inherited properties in sub-class. The solution should provide high performance and scalability. The problem, I am trying to solve is like this.

    base table row
    id = 1
    col1= A
    col2 = B
    col3=C

    inherited table row
    id = 1
    col1 = D
    col2 = null
    col3 = null

    id will map inherited to base rows.
    null value in inherited table means I am not overriding any values so should select from base.

    I want the fastest way to query inherited table (with look-up or join against base table) that will give following output

    id=1
    col1 = D
    col2 = B
    col3 = C

    The base table in actual project has 50+ columns and millions of rows. Each Base table row can be inherited hundreds of times into inherited table. Inherited table has its own surrogate key (not shown here). Over-ridden values will be roughly 5-10% of the time. Remaining 90-95% of the time default values from base table will be used. Copying entire row from base to inherited table and then changing over-ridden values is not an option. The existing design, in fact, works this way but is a nightmare from data audit perspective.

Share This Page