MS SQL Server - The Great Lookup Table Debate

Author

The Great Lookup Table Debate

Matt Morris

2005-07-27, 9:23 am

We have an internal discussion among a development team that produces a
suite of client and web applications that all use the same shared database.
One portion of the developers believes that we should use lookup tables in
most cases to provide greater schema readability, referential integrity and
prevent enumeration collisions as multiple applications and suite
development teams are hitting this same shared database.

The other portion of developers believes this will bloat (quadruple number
of tables from 70 to 250 or so) the schema design, may harm performance and
will provide little benefit and prefer using check constraints in place of
lookup tables.

Our questions are two fold.

First, does adding lookups for referential integrity affect performance in
any notable way?

Second, from a general standpoint, if performance is not an issue, how much
weight would be placed on the readability and referential integrity aspect
of having lookup tables? I know referential integrity is important and feel
awkward asking such a question but I'm basically asking for a estimated
quantification of it's importance.

Thanks!

Jacco Schalkwijk

2005-07-27, 11:23 am

Using lookup tables might perform slightly worse than using check
constraints, although you would have to test to quantify this. I assume that
the difference will be small, because lookup tables tend to have only a few
rows.

The main (and in my opinion overriding) argument against using check
constraints instead of lookup tables is that if you have to add another
enumeration value to a lookup table/check constraint, you only have to add a
row to your lookup table where you would have to change the code to keep the
check constraint up to date.

Also, if you use lookup tables you can have an expanded version of your
enumeration value, a description if you like, in the lookup table, where
when using check constraints you would have to include this expanded value
in possibly multiple different locations in your code.

--
Jacco Schalkwijk
SQL Server MVP

"Matt Morris" <nospam@us.pwc.com> wrote in message
news:u94p9brkFHA.1416@TK2MSFTNGP09.phx.gbl...
> We have an internal discussion among a development team that produces a
> suite of client and web applications that all use the same shared
> database.
> One portion of the developers believes that we should use lookup tables in
> most cases to provide greater schema readability, referential integrity
> and
> prevent enumeration collisions as multiple applications and suite
> development teams are hitting this same shared database.
>
> The other portion of developers believes this will bloat (quadruple number
> of tables from 70 to 250 or so) the schema design, may harm performance
> and
> will provide little benefit and prefer using check constraints in place of
> lookup tables.
>
> Our questions are two fold.
>
> First, does adding lookups for referential integrity affect performance in
> any notable way?
>
> Second, from a general standpoint, if performance is not an issue, how
> much
> weight would be placed on the readability and referential integrity aspect
> of having lookup tables? I know referential integrity is important and
> feel
> awkward asking such a question but I'm basically asking for a estimated
> quantification of it's importance.
>
> Thanks!
>
>
>
>

Matt Morris

2005-07-27, 8:23 pm

I appreciate that response. In your reply, you don't mention anything
regarding the size of the schema in terms of the number of tables. The most
outspoken views in opposition of lookup tables relies heavily on this point.
Assuming we were to use a naming convetion for these types of tables, should
there be any cause for concern at the sheer increase in the number of
database tables? 70 --> 250?

"Jacco Schalkwijk" <jacco.please.reply@to.newsgroups.mvps.org.invalid> wrote
in message news:e37OLmskFHA.1048@tk2msftngp13.phx.gbl...
> Using lookup tables might perform slightly worse than using check
> constraints, although you would have to test to quantify this. I assume
that
> the difference will be small, because lookup tables tend to have only a
few
> rows.
>
> The main (and in my opinion overriding) argument against using check
> constraints instead of lookup tables is that if you have to add another
> enumeration value to a lookup table/check constraint, you only have to add
a
> row to your lookup table where you would have to change the code to keep
the
> check constraint up to date.
>
> Also, if you use lookup tables you can have an expanded version of your
> enumeration value, a description if you like, in the lookup table, where
> when using check constraints you would have to include this expanded value
> in possibly multiple different locations in your code.
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
>
> "Matt Morris" <nospam@us.pwc.com> wrote in message
> news:u94p9brkFHA.1416@TK2MSFTNGP09.phx.gbl...
in[color=darkred]
number[color=darkred
]
of[color=darkred]
in[color=darkred]
aspect[color=darkred
]
>
>

David Portas

2005-07-27, 8:23 pm

More tables is sure to be superior to a single one-size-fits-all monster
table. The purpose of a table is NOT support UI functionality with
"lookups". Tables exist to model data. The data is far more valuable than
any application that uses it. The guiding principle should therefore be to
identify the functional dependencies and create a normalized data model that
represents the data in a consistent manner without redundancy or potential
anomalies. If you keep that goal in mind then you wouldn't even need to ask
this question.

--
David Portas
SQL Server MVP
--

2005-08-01, 8:23 pm

lookup tables with proper referential integrity are far better imo.
makes your db easier to visualize and your data cleaner.

Matt Morris wrote:
>
> We have an internal discussion among a development team that produces a
> suite of client and web applications that all use the same shared database.
> One portion of the developers believes that we should use lookup tables in
> most cases to provide greater schema readability, referential integrity and
> prevent enumeration collisions as multiple applications and suite
> development teams are hitting this same shared database.
>
> The other portion of developers believes this will bloat (quadruple number
> of tables from 70 to 250 or so) the schema design, may harm performance and
> will provide little benefit and prefer using check constraints in place of
> lookup tables.
>
> Our questions are two fold.
>
> First, does adding lookups for referential integrity affect performance in
> any notable way?
>
> Second, from a general standpoint, if performance is not an issue, how much
> weight would be placed on the readability and referential integrity aspect
> of having lookup tables? I know referential integrity is important and feel
> awkward asking such a question but I'm basically asking for a estimated
> quantification of it's importance.
>
> Thanks!

Sponsored Links