Home Forums Database Design Relational Theory Is having a lookup table that is for multiple entities a common practice? RE: Is having a lookup table that is for multiple entities a common practice?

  • IMO, it's an extremely bad practice.

    It reduces database integrity, because foreign keys can't have filters on them, and so there's no way for the DB to prevent a country's lookup value been used in a StatusID or a Status's lookup code being used in the GenderID column (assuming there are foreign keys).
    It encourages mistakes, like 'Active' being added to the lookup table with the ST (state) code, rather than the SS (Status). 'Active' in a lookup of states and provinces would be easy to spot, in a OTLT it's much harder to spot.

    And on the design theory side, a table should have one thing in it. A table should have transactions or customers or vehicles, etc. When there's a table that contains cars, planes and watermelons it suggests something is wrong.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass