August 11, 2004 at 6:41 am
As you can see in article, real life scenario 2 is more complicated than the one table. It consists of 4 tables (in my case). Based on 4 tables you DO NOT HAVE MORE THAN 1 LEVEL of dependency. And codes are separated by groups. Plus, I store ONLY GENERIC codes in the structure. The codes that are not company specific. Company specific codes are in their own tables based on scenario 1. This allows reuse generic codes and separates them from user specific ones. If you have one database or one big data warehouse it may be irrelevant. But if server has 200+ databases it will be much less overhead to keep generic code tables into on separated database. In my case, 2-3 new databases created in server weekly. All of them have the same generic codes and it will be complete "madness" try to synchronize them when changes necessary.
Each design should be choosen carefully for each specific case! As always each one have pluses and minuses. It is mean choose ideas carefully for you database scenario.
July 27, 2005 at 7:42 am
Peter,
Can you explain a little more what you mean when you say that a simple function call is much better than a join to do a code lookup? Are you speaking of a user define function in sql server? The reason I ask is that I'm still learning of course (always) and so I'm just curious.
EDIT: Ok I just now saw you're second post and I see that you are talking about a user defined function. Now how exactly would you use a UDF with a lookup table? Do you have an example?
Thanks,
Nate
January 8, 2006 at 8:55 pm
This is one of the worst practices from design and performance standpoints.
First, design-wise, why give up foreign keys? As mentioned in an ealier post, that is the WHOLE POINT of RDBMS's. That is Celko's issue with consolidating codes into a single table. Second, performance-wise, does any RDBMS engine process trigger code as efficiently as its built in routines for checking foreign key validity? The answer is no.
Top priority has to be the integrity of the data. It is not ease of adminstration.
Make the right choice, design a better database. Then, look for a way to maintain your codes better. Maybe, a central code database that publishes updates to many databases with common structures is a better approach as it does not compromise the database design.
- Jay
October 19, 2007 at 1:09 am
There is a point of view (well actually mine) which says that you should not have any lookup tables at all.
The reason being that every description in a lookup table should be available in the 150+ languages of the planet ... let the GUI do the translating!
October 19, 2007 at 6:09 am
Why wouldn't you place the language translations in tables?
Do you really trust that every application running against your database is going to decoding correctly? For the same reasons, you would not trust the applications to enforce referential integrity, I would not trust them to decode data expressions.
- Jay
October 19, 2007 at 6:42 am
The meaning of a code is entirely subjective and has nothing to do with normalization ... it might actually be a picture or several pictures or an mp3/mp4
October 19, 2007 at 9:02 am
roger clarke (10/19/2007)
The meaning of a code is entirely subjective
If you have a code WHS123 that is a 'local distribution warehouse', it may have many translations into local languages, but is is entirely objective. You would not want one application to reference this as a 'regional manufacturing warehouse' and another calling is 'building xyz' because that is what they think it should be called. If both of those are also accurate descriptions of WHS123 that are synonymous to the actual meaning, they too can be stored in the database.
- Jay
October 20, 2007 at 5:56 am
objective=0|1 (digital) subjective=analog ... you have missed the entire point
October 20, 2007 at 6:22 pm
I am trying to understand the point of letting the GUI do the translating.
For instance, I have a database that contains the transactional data for one of the systems operating the business. There are actually two applications that operate primarily in this database. There are two others that store ancillary (to those systems) data there. There are also at least three different reporting platforms. Additionally, there could be any number of home-brewed reporting apps in Access or Excel.
In such a scenario, when a code description changes, would you want to make that change once in a database. Or, would you prefer to update that code in every application that references it? Or, maybe make the change in one of the systems and let the others fall out of date?
The point is that this data describes entities in your database and they belong in that same database for the sake of data integrity.
- Jay
October 21, 2007 at 8:14 am
With apologies to Elizabeth Barrett Browning ... Let me count the ways
There are two things here ... a real problem and a suggested solution
the problem is ... the infinite no of translations of any word or phrase (even in the same language ... Mr John S Jones, jonny boy, Mr Jones)
the (extremely vague) solution is ... let the GUI figure it out
October 21, 2007 at 1:02 pm
Why can the user preferred names be saved in the database with the 'official' versions of the names? Why would you trust a number of GUI's to translate everything consistently?
You still have not defended your position of why the GUI should have the translations versus keeping that data (not matter how customizable) in the database. You have merely stated that the GUI would be better than the database. Why?
- Jay
October 15, 2008 at 7:05 am
Interesting topic but I am dumbfounded by the table and field naming conventions used in the examples given.
It makes the article really difficult to read.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply