Lookup Table Madness
-
Thanks for the comment. This is just was my point. There are many different ways to do a job and architect. And I was trying to show it. But it looks like that some people could not (or do not want) to hear that each project may have various scenario based on the business, size of data, data itself, even corporate culture, and other database design. And in a lot of cases a design may have a mix.
-
are many different ways to do a job and architect. You are correct in this...there are many different ways. The thing you're missing is that most ways to do things are sub-optimal. In fact, once could say all ways but one are sub-optimal. Still, some designs are worse than others, as the author of this article was pointing out.
If you don't like working with relational database correctly, then please DON'T WORK WITH THEM! Store your data in XML or flat files instead. If you do want to work with a database, LEARN HOW TO USE IT.
If you feel like the relational model is flawed, then don't use it. But don't try to use it and ignore it at the same time.
As it's been said here many times; there are basic principles that form the foundation of relational structures, and integrity constraints and normalization are big parts of that. "One big lookup table" designs violate both.
Signature is NULL
-
First, I am an author of the article. Second, pure scientifically developed relational database is not very often good for the practical use. Third, it is like endless question what database engine is better - Oracle or SQL. Oracle fans saying Oracle, SQL Server fans saying SQL Server. But reasonable people saying it is depence from environment, tasks, money, experience, and so on. There are no absolute trueth. And if you think that you are right - then you are right.
-
You are right in that different problems lend themselves to different solutions. Thus, Oracle has strenths best suited for particular types of problems, while SQL Server others, but each with their respective weaknesses. Deploy the tool that best suits the purpose.
However, a tool is a tool: it was designed for a particular task in mind and the misuse of it—the attempt to use it for purposes for which it was not designed—is folly.
There are myriad DBMS products out there, each with potential usefullness in specific situations. What has been said here, however, is that once a tool has been chosen, you should live within the scope of its design. If you want, or need, to do something different, then you should choose a different tool, or build your own.
Would you use a butter knife as a screwdriver—come on, I know you have—but wouldn't you have rather had the screwdirver to begin with?
If you choose a relational DBMS engine, or at least a SQL one, you should live within its axioms. If not, the results those axioms garauntee are no longer valid. Why would any WANT to use invalid results?
What the author professes is that the RM is the only model for data built on such axioms. So, it is easy to see why others would attempt to use this tool for a great many tasks for which it was not designed: there are very few other data tools available. That, however, does not suggest that you should misuse it; it merely shows the gap between the existing SQL solutions and a fully recognized and supported RDBMS implementation.
Perhaps you should spend some energy building a new tool or assisting with the push for a real RDBMS product.
-
The arguments may be endless. But database is primerely is a data storage. In the article, I did outlined different ways to construct lookup tables but it is up to you which way to use it. Doesn't mean if you using C++ that you should use it up to the full extend. For the small tasks you may want to ignore it's OO features but rather use it as structured C. Again, the way of use may be different, but I do agreed that if you trying to design against relational rules in RDBMS you should be very carefull and you should have very good reasons and explanations why you do it this way. I would like to say it is prerogative of very experienced architect who knows why he/she is taking the non standard road. In my case I show only the many ways to get job done. And as you can see I didn't dicuss the question which way is better. When you driving to a work there are many roads (I am using at least 4 different roads based on weather, driving to or from work, community events...). And non of them really always better then another. Even one roader in most (not all) cases better.
-
I would agree that a discussion as this would last forever because although we are discussing the same topic, there is confussion in the arguments being made.
You must seperate the diffence between multiple acceptable solutions to a problem verses the different tools for a solution. You CAN NOT USE AN RDBMS for non-relational work.
Here is an example: simple addition,
4 = 1 + 3 = 2 + 2 = 3 + 1 = 1 + 1 + 1 + 1 = 1 + 1 + 2 = 2 + 1 + 1 right?
Those are examples of multiple solutions to the problem...all acceptable, but only from the standard axioms of integer mathematics. The reason I can use any of the solutions above is precisely because they are all based on the same foundation and are logically consistent. Therefore, I am free to choose among them as far as what is appropriate for the problem at hand. BUT THEY ARE ALL THE SAME SOLUTION, just different expressions using the same rules.
Now, it is possible to construct an Algebra such that 1 + 3 = 0. And, I may need such an Algebra for some specific problem. In this case, it would be reasonable to call it an Algebra, but it WOULD NOT BE THE STANDARD. It would also be riduculous for me to try to explain what the number 4 is in such an algeber. It is equivalent to 0, but that does not make it 0.
It is the same with this discussion. There are DBMSes that are based on the Relational Model of Data, and for its use, you must hold to its principles, which the argument is that this would exclude the use of such "Muck" tables.
That is not to say that there are no other priciples and DBMSes that can be built off of them: clearly the Multi-value DBMS vendors would like you to believe so. But this also does not mean you can use an RDBMS as a multi-value DBMS platform. That would be out of context, certainly given the current SQL DBMS implementations.
I would argue that these global lookup tables "types" would be better suited for the OO-DBMS systems. So, if you're married to the solution, then choose that tool appropriately. But don't stink up an RDBMS with garbage that doesn't belong there.
-
If you are relational DBMS purist, then you are the purist. I am the guy who is very practical, despite my very high teorethical degree and very high level of math skills. And I do not want to argue about a theory in a practical live. So be it. We are representing different design styles. Actually you are representing only one while I am trying to look the whole task and many methods that will give me the right result. While I may choose pure relational model in 95% of cases but I will be very carefull with the model in 5% another cases. Sometime EAV model (completely weird design) can be very suitable.
-
Although I agree with most that the article is a bit lengthy and wordy, I strongly agree with its content and purpose. The art of mixing and matching codes into a sole container makes it easier for programmers to code but it surely turns, in my opinion, into a poor database design.
-
Although I agree with most that the article is a bit lengthy and wordy, I strongly agree with its content and purpose. The art of mixing and matching codes into a sole container makes it easier for programmers to code but it surely turns, in my opinion, into a poor database design.
-
Very interesting and insiteful... Somehow the good design concepts are always the simplest
I was really surprised at how many people thought you could improve on your writing style! I mean who couldn't improve... (Tom Clancy, are you listening
)Please keep up the good work, and if I have to read the article a couple of times to get your complete meaning, then I probably just needed to come up to speed on a new concept or two anyway.
-
Very interesting and insiteful... Somehow the good design concepts are always the simplest
I was really surprised at how many people thought you could improve on your writing style! I mean who couldn't improve... (Tom Clancy, are you listening
)Please keep up the good work, and if I have to read the article a couple of times to get your complete meaning, then I probably just needed to come up to speed on a new concept or two anyway.
-
I agree with you on MUCK tables. Here's a great example from our ERP system (Visual Manufacturing from Infocor). One table stores all units of meaure for parts. So, the careless user can easily create a part that has the following dimensions:
Weight: 10.5 eaches
Length: 5 pounds
Width: 8 pairs
Height: 3 cases
Fie on MUCK tables!
-
Thanks, I think...
Here's a long winded reply.
"The art of mixing and matching codes into a sole container makes it easier for programmers to code..."
NO!, that's the point, it doesn't make it easier to code. The ONLY "benefit" is that there are fewer tables, but if those constraints aren't enforced at the data level--by proper design and declarative constraints--then the programmer's job becomes MUCH more difficult because now they have to code all those constraints themselves.
I understand why people say it is easier, but they are ignorant of the likely problems and/or choose to ignore them. This, of course, does not make the problem go away. At some point somebody will have to clean up the mess or else throw the whole thing out and start over. Enhancements become much more difficult to do because the data model is nowhere near as meaningful as it ought to be etc...
An anology I came up with for jobs like this is trying to seperate the toilet paper from the sewage in an outhouse. At best it is a nasty frustrating job, at worst it's a nasty frustrating job that also happens to be impossible.
The reason I wrote the article is because I am tired of trying to decipher and clean up after people who do this kind of crap and then pat themselves on the back for being so clever. It isn't cleaver, it isn't smart, it isn't logically sound, and since subtlety seems to be my strong suit, it is just downright stupid! End of story.
/*****************
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
*****************/
-
All I can say is Amen.
-
BTW Ivan, I did not mean to sound as if I was personally taking you to task.
One more comment about your post though. You state that the practice leads to poor database design "in my opinion." There is no reason to qualify it by saying it's just your opinion. It IS poor database design regardless of anybody's opinion.
/*****************
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
*****************/
Viewing 15 posts - 76 through 90 (of 202 total)
You must be logged in to reply to this topic. Login to reply