| Author |
 Topic  |
|
byrmol
Shed Building SQL Farmer
Australia
1591 Posts |
 Posted - 06/04/2006 : 18:55:03
|
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..
DavidM
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 |
 |
|
|
cmdr_skywalker
Posting Yak Master
159 Posts |
Posted - 06/04/2006 : 23:39:40
|
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! |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 06/05/2006 : 06:37:19
|
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?"
Kristen |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 06/05/2006 : 07:11:27
|
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”.
CODO ERGO SUM |
|
|
|
Topic |
|
Common Lookup Tables
