Lookup Table Madness
-
David,
Hmm, let's see... For a table that has a list of countries... How about "Countries"?
Internationalization and schema naming schemes gets into some strange territory. The philosophical question must be raised: Do vendors support their vended databases, or is the onus on the client? I am definitely in favor of the former approach, being generally on the vendor end of the equation. Clients have all too often messed around with databases they shouldn't have touched, and I had to fix them. Given that attitude, I can safely say that tables should be named in the native language of the vendor.
If you take the opposite attitude, which I have a feeling you might as it sounds like you support vended databases, the question is much more difficult to answer. I guess either A) tables should be named in whatever language most of the clients use, or B) if possible all access via the application can be made through stored procedures, which will safely encapsulate the schema. Then, different scripts can be made for whatever languages are necessary. I don't believe this would be too difficult to maintain; it would be a simple matter of doing a global find and replace (the difficult part would be identifying what to replace.)
--
Adam Machanic
whoisactive -
David,
Let me guess...The vendor in question probably doesn't use any sort of DRI either. That way nobody can make heads or tails of their database design (assuming there's any sense to be made of it). I suspect that this is mostly an effort to extract the maximum in consulting fees from their poor customers. The only thing they could do to make matters worse is to name the columns something like Col1, Col2 and of course make them all varchar.
I have seen enough of this kind of nonsense that I have pushed my employers to insist on seeing the database design prior to signing the contract. Any vendor that won't play ball, I strongly argue against any further consideration of their product. What nonsense!
Lastly, don't ever mistake what a vendor does to constitute anything like "best practice." It is obvious to me that the majority of vendor supplied databases are marginal at best, most are poor. They all tend to completely disregard the concept of data independence. I suspect this is a large reason why the relational model has been burdened with so many negative perceptions. It is almost always a nightmare to integrate one system with another and the reason has nothing to do with any weakness of the relational model it is entirely due to the fact that the vendors don't properly design their databases and thus must enforce many, if not all, of the business rules in code which is completely hidden from the customer. Thus you have a very difficult time in figuring out how the data should be used and how it relates to anything outside that particular system.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Yep, no DRI.
A few years back there was an discussion on this site about leaving out DRI in order to get the maximum throughput on the database.
The example given had all DRI was handled via the stored procedures.
There was another guy who had absolute bare minimum indices on his server. He had two servers supporting a website and on odd days one server took the load, on even days the other took over.
Whilst one server took orders from the web site the other did all the back end processing.
The object was to get the maximum performance out of the minimum cost.
I've run into a situation in the past where I've said something shouldn't be done for performance reasons and management have simply got someone else to do it.
It works fine for 6 months and then the expected performance problems crop up.
Instead of management remembering that they were warned they remember that.
a) In the first place you couldn't do the simple thing that has been working fine for six months.
b) Its your fault that it doesn't work now!
-
It seems that Mr. Peterson has political issues than technical ones. I mean why are people with no idea about normalization techniques getting involved in the database design architecture anyway?
It's too bad that he has to rant about this issue, because its clear cut to me. Although it is interesting that over zealous OO developers may be trying to change the whole world in the way they see things. Can't we just all get along?
-
The problem is that in the Distributed Architecture world, oftentimes, the application developer is also the systems developer, middleware developer, database developer, and, yes, the presentation layer developer.
We have lost focus for the Achitectural, Engineering, then development process. You would rarely see this sort of behavior in the mainframe world, in shops where security, specialists, and seperation of job function rule.
Unfortunately, this means trained Database Engineers need to fight for this sort of education to be passed down to the application development community. God knows, we are not brought in during the design phase, if such a phase ever existed to begin with.
Anthony L. Thomas
-
Lol! We can get our databases down to just one "Thing" table with two columns. Awesome.
Of course, come to think of it, we may laugh at today, but it'll be the latest trend tomorrow. Simple Holistic Integrated Tables.
John Scarborough
MCDBA, MCSA -
Don't laugh...they're already here. Not to name names, but one application already exists with a single table, two columns: an IDENTITY and a CLOB, all with XML.
Sincerely,
Anthony Thomas
-
Of couse I am coming into this a bit late. However, some comments. For those coming into this discussion, who maintain the single complex look up table is practical, the real world is more complex - for example:
1. Values of types or statuses or categories (or whatever this data is described as) contained in look-up tables vary over time. Thus we need to include validity, currency and date-time attributes.
2. In [at least] a financial application it will be necessary to track changes (e.g.,audit trail needs - Sarbanes Oxley pressures) made to the look-up values, with before and after images of the data, with details of which user made the change, and perhaps, which user authorised the change. Thus we need to include change and approval attributes, with user relationships.
With these additional requirements, the simple three column compound look-up table has disappeared. Domain constraints and other needs have changed the initial premise, and perhaps even added a lookup to the lookup table - now all that in one table creates some interesting issues.
Conclusion - stay with normalisation - it works.
John Hindmarsh
-
How many rules of normalization? 12. How many we are using? 3-5
And you are not saying that doctor Codd was developing useless 7 rules. The same point in article.
To show that ability to store data (lookup data) with 3 columns is exists. In some situations it may not be justified to use this type of design. In another it may be fine. Especially, if there are multiple various lookup data elements but number of rows for each type is small (for example 3-4).
Again, some situations may use combinations of traditional normalized structure and the one is described in article. Please read about EAV design as different design approach. With some cases (medical trials, for example) it may be very useful.
-
I'm sorry but you are confused. Codd's 12 rules are not about normalization, rather they define the characteristics of a Relational Database Management System. There are between 5 and 8 forms of normalization (depending on how you count them and whether you include the 6nf as proposed by Chris Date and Hugh Darwen et al...)
Now, the fact that there are no DBMS platforms out there that conform to all the 12 rules has NOTHING to do with normalization. The lack of support by the vendors and particularly by the SQL Standard is a problem (particularly the rules about physical and logical data independence), but again this has zero to do with normalization per se.
The number of columns or rows once again has little or nothing to do with normalization.
Entity Atribute Value (EAV) design is nothing new. And rather than being a step forward in database design, it is actually a significant step backward. Data integrity is sacraficed for "flexibility" but that flexibility is a costly illusion. The cost is to be paid in the loss of data integrity, any kind of meaning to the structure of the database, ease of updating, and ease of querying the data for starters. In the end the flexibility is lost too since now your application needs to understand what the values in a column mean rather than being able to know the meaning of a value because it is in a given column (hard-coding your apps). EAV is partly what I had in mind when I wrote the article and I stand by my statement that those who pursue this design philosophy do so because they lack an understanding of data management fundamentals and their importance.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
At the end, the mind should not be locked. And if in specific cases something is valuable, all options should be opened. And I am opened to all ideas based on specific cases. If you read an article carefully, you pay attention that I am not saying that this design should be used often (or even used at all). This is like theory. It is exists and ready for use. And I am successfully using it from time to time in small databases
Good luck with only one open design consideration (one pill solve all decises or NORMALIZED ALWAYS).
-
I agree that the mind should not be locked, but I also maintain that it shouldn't be unguarded either. A totally open mind is like a trash can, you never know what kind of garbage might find its way in...
Just because a particular approach "works" doesn't mean that it's correct. Data management using flat files "works" too, but there are problems with it and other data managment methods. These problems were addressed in the Relational Model which is based on set theory and predicate logic. Normalization is a necessary prerequisite to enjoying the benefits offered by the RM. The RM and normalization are not the same thing, but the one is dependent on the other. If you toss out normalization you also must live without the benefits of the RM.
The main problem is that most people who recommend non-normalized and thus non-relational data management (OODBMS, and XML are prime examples along with MUCK tables) are ignorant of the problems that they are causing for themselves.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Then in your mind, object-relational and object oriented database database design is trash as well. Lobachevsky's and Newton's teories are different. One working in space and another on the Earth. In yor mind Lobachevsky is wrong because you are on Earth. So be it!
-
Yes and no. I am not a physicist and can't speak on the realative merits of Newton's and Lobachevsky's theories, so I won't attempt to. But you are correct when you say that the OODBMS design is trash. Why? Precisely because it isn't based on a defined, cogent theory at all. At least the hierarchical databases of the past were based on graph theory. Graph theory is far too complex to lend itself to the task, but at least there was a foundation of sorts. OO can't claim nearly so much. It, and MUCK tables, etc... are literally buildings without foundations. You can live in them if you wish, but don't be surprised when they fall down around your ears.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Excellent article! I don't understand why so many find it difficult to follow or understand. I guess this just illustrates the difference between database architects and application developers.
I've fought the same battle with varying degrees of success. Even left one project because of issues like this, knowing what the long term results would be.
Viewing 15 posts - 61 through 75 (of 202 total)
You must be logged in to reply to this topic. Login to reply