Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Common Lookup Tables
 Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

Shed Building SQL Farmer

1591 Posts

Posted - 06/04/2006 :  18:55:03  Show Profile  Reply with Quote
This isn't about Lookup tables is it Bill?
It's about minimizing impact of implementation during model changes.

As Nigel suggested, stored procs and views can help with the abstraction as well as some meta data and a simple middle tier framework.

>>We're also investigating some code generation utilities (CodeSmith)

Amen brother!! It took me nearly a month to design and write some templates for my current project...It's written nearly a million lines of code on this project alone.

Eric, if you are reading... I promise I will buy a copy of the new version after I sell 10 units...

EDIT: typo..

Intelligent Design is NOT science.

A front-end is something that tries to violate a back-end.

Edited by - byrmol on 06/04/2006 19:01:53
Go to Top of Page

Posting Yak Master

159 Posts

Posted - 06/04/2006 :  23:39:40  Show Profile  Visit cmdr_skywalker's Homepage  Reply with Quote
Look up tables helps greatly in queries, much similar to dimensions in data warehouse. However, if you implement it through SQL using CHECK constraints, it might not be the optimal solution. Remember, there are several ways to implement business constraints (from Application through codes). Look up table was not intended to minimize the load in DML but rather a mechanism in case you need to change business context or attributes of the BUSINESS OBJECT. In that way, you don't have to go through the all the databases (replicated) and tables just to add an additional attribute that is part of the primary entity. I think that this is the primary use of lookup tables-- scalability and manageability aside from old data integrity.

Try using data warehouse capability (with drag and drop) and see if you don't use somekind of lookup table. As a domain integrity, application codes can be the primary enforcer but having a constraint ensures that underlying data model and application are in sync (if in case someone passthrough the Application protection, i.e. through SQL Injection, it won't work because you have a safety net).

May the Almighty God bless us all!
Go to Top of Page


United Kingdom
22859 Posts

Posted - 06/05/2006 :  06:37:19  Show Profile  Reply with Quote
Actually, thinking further about the FK issues (CLT doesn't have an easy FK route, separate tables would of course) do application programs just use that as the error catch?

I mean: We have a test in the SAVE routine so that we can provide a nice friendly error message if an attempt is made to insert a row with a missing FK companion. We don't rely on the FK to intercept the error for us. Of course IF the FK is in place it a) saves us from ourselves and b) covers us for ad-hoc imports and the like.

But I wonder what people's opinions are on "how major is the absence of an FK is for CLT type codes?"

Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 06/05/2006 :  07:11:27  Show Profile  Reply with Quote
Originally posted by Kristen

Actually, thinking further about the FK issues (CLT doesn't have an easy FK route, separate tables would of course) do application programs just use that as the error catch?...

Our application developers never consider the possibility of errors, so it is kind of the default.

I think that FK constraints are most valuable during development/testing to make sure the application code and stored procedures are at least getting data from the correct table.

I had an argument a few weeks ago with a developer who insisted there was something "wrong with the database" because it wouldn't let him insert data that violated a FK constraint. He insisted the procedure was correct. Of course, it wasn't.

DRI is a big thing to give up. Without it, you are just hoping that everything is properly tested. I think the codes table makes it even worse, where you hope your employees don’t end up with a job code of “Midnight Blue”, and a product doesn’t get a color of “Assistant Office Manager”.

Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000