Anticlue

i fail to see exactly how extensible or easy-to-use this is, besides the fact that you 'might' have less code... even though I doubt the difference shd be anything significant

it also appears you are structuring your data to fit your code; imho, it shd be other way around... what this means is that, when minor things in your code change, you don't have to make changes to your data to make that fit too... as an example what happens when "Service" becomes "SVC"... hyphotetical, but possible :) ... before long, you'll start maintaining a reference table for reference types ... :P

there's value in keeping your code/data model as separate and as simple as possible :)

I'm beginning to think that this approach is more popular than I would have thought. It seems like it would go against everything I know to mix all the different types of data, but I see it more and more in larger applications. I see you are using medical terms and the last place I saw this was in the Cerner Millennium system. Do you think there are performance gains as well to storing reference data in a single table?

Hi Elyse,

I don't mean to be negative, but I think this style of DB design has flaws. It only works as long as the different types of "reference data" have the same taxonomy - in this case, a Mneumonic and a description.

1. What happens when I want to store State and Country data in the catch-all lookup table? Or ZIP codes, with lat and long info?

2. It sort of defeats the purpose of normalization to some degree - you wind up with a ton of duplicate strings in the "type" column. To fix this, you can add another table refLookupType, but then...when not just use real, entity-dedicated tables to begin with at that point?

I've been down the road with a very large application that did just what you're describing - the table became a mess, had tons of columns to hold entity-specific stuff, sometimes the columns held something nothing like what they were named because "we can just use this column for that," etc.

I did the same thing 4 years ago to combat massive proliferation of lookups. I now have a 2,500 row lookuptable and this table contains the list of lookups as well (set refType='Valids' and refMnumonic='age_class' and refDecsription='Age Classes'. Then refType for the actual age classes is set to 'age_class' - So the code also maintains the list of lists. Also added a definition column and a flag to show or hide a row. My code can also handle addng the next sequence to a valids list - the refMneumonic may be numeric and not alpha-coded. Only have to be careful about deletes and refMnumonic changes