tag:blogger.com,1999:blog-7631414.post109836970950995223..comments2023-05-31T04:00:19.993+00:00Comments on Tony Andrews on Oracle and Databases: OTLT and EAV: the two big design mistakes all beginners makeTony Andrewshttp://www.blogger.com/profile/16750945985361011515noreply@blogger.comBlogger23125tag:blogger.com,1999:blog-7631414.post-63572818182917485042014-11-16T00:56:44.978+00:002014-11-16T00:56:44.978+00:00Now for the semi-EAV table.
create table person(i...Now for the semi-EAV table.<br /><br />create table person(id int(8), section varchar(10), jsondata text, primary key(id, section));<br />create table person_search(id int(8), section varchar(10), name varchar(30), value varchar(100), primary key (id, section, name), index(section,name,value));<br /><br />The person_search table is my semi-eav. I use this table for adding "search" Anonymoushttps://www.blogger.com/profile/05857306242629650955noreply@blogger.comtag:blogger.com,1999:blog-7631414.post-82404668520859951002014-11-16T00:56:19.249+00:002014-11-16T00:56:19.249+00:00While this may have been true 10 years ago. Thing...While this may have been true 10 years ago. Things have changed drastically. If you only have a couple of "lookup" tables then you can use separate tables, but if its something as simple as countries or states, then its probably better to hard code the array into your application than to use precious network activity for something that rarely changes. Its as silly as having a "Anonymoushttps://www.blogger.com/profile/05857306242629650955noreply@blogger.comtag:blogger.com,1999:blog-7631414.post-7820493717384996692012-02-21T15:02:38.185+00:002012-02-21T15:02:38.185+00:00As a developer, not a database guy, I'm all fo...As a developer, not a database guy, I'm all for the OTLT model, but I only ever implement it with a unique alt_key as suggested by lewisc.<br /><br />Lets say I didn't use OTLT and I created a table called Gender with columns gender_id, gender_code and gender_description. I don't see how this stops somebody inserting into the table like so:<br /><br />insert into gender(1, 'djshttps://www.blogger.com/profile/02473707102406409704noreply@blogger.comtag:blogger.com,1999:blog-7631414.post-77843396667421459652011-10-30T15:55:51.658+00:002011-10-30T15:55:51.658+00:00I wrote about this in series on Database Design Mi...I wrote about this in series on Database Design Mistakes To Avoid. I called it MUCK. <br /><br />http://rodgersnotes.wordpress.com/2010/09/21/muck-massively-unified-code-key-generic-three-table-data-model/<br /><br />To me, a good db design should eliminate code, not cause it:<br /><br />http://rodgersnotes.wordpress.com/2010/09/14/database-design-mistakes-to-avoid/Rodgerhttps://www.blogger.com/profile/13977796396343653710noreply@blogger.comtag:blogger.com,1999:blog-7631414.post-4534625290825223772011-05-12T02:28:27.437+00:002011-05-12T02:28:27.437+00:00oh man. I just escaped from a job with a EAV based...oh man. I just escaped from a job with a EAV based CMS. I didn't know there was a name for it. It was horrendiously slow. Ha! and it wasn't just and EAV, the EAV documents where joined together in a tree, with child documents...<br /><br />The simplest possible page actually had 4 levels of nesting,<br />so to get the data to display the simplest possible page (not counting the stuff dmt10https://www.blogger.com/profile/09824949527326430525noreply@blogger.comtag:blogger.com,1999:blog-7631414.post-14916281625761889452010-09-19T00:53:37.703+00:002010-09-19T00:53:37.703+00:00I agree with KBR.
Two domains that need to use EA...I agree with KBR. <br />Two domains that need to use EAV would be <br /><br />1) Medical Clinical applications (there are potentially more symptoms to diseases that can be supported by the max limit of columns per table in any current database implementation. And not every patient has these same symptoms - leading to very sparsely populated tables. And no one can predict what other new symptoms Unknownhttps://www.blogger.com/profile/06763316755413558857noreply@blogger.comtag:blogger.com,1999:blog-7631414.post-67836367945016744312010-01-12T19:28:26.013+00:002010-01-12T19:28:26.013+00:00For crying out loud, use spatial for EAV.For crying out loud, use spatial for EAV.Unknownhttps://www.blogger.com/profile/16997636339400713220noreply@blogger.comtag:blogger.com,1999:blog-7631414.post-77955722327819738742009-12-17T10:13:16.849+00:002009-12-17T10:13:16.849+00:00Hi,
Your comments re Look-up tables are spot-on, ...Hi,<br /><br />Your comments re Look-up tables are spot-on, why would you do this - in fact why would a software developer want to do this(?), don't they use separate enums which serve a similar purpose?<br /><br />As for EAV, I have to disagree with the OP. Those business models, and there are many, which necessarily need to store an unknown set of columnar data, have only three approaches KBRhttps://www.blogger.com/profile/11812754051848648952noreply@blogger.comtag:blogger.com,1999:blog-7631414.post-18361350885393899812009-09-08T02:48:31.468+00:002009-09-08T02:48:31.468+00:00Scott,
If you have an with lookup with 500,000 ro...Scott,<br /><br />If you have an with lookup with 500,000 rows *give it another table*. This is not an in scope requirement for a lookup table.<br /><br />If you have a lookup which needs different columns (audit) *give it its own table*. On the other hand if all lookups need audit the OTLT wins even more.<br /><br />You have not lost any flexibility, just do what makes sense.Unknownhttps://www.blogger.com/profile/18001815881633914931noreply@blogger.comtag:blogger.com,1999:blog-7631414.post-64385820401082941942009-09-08T02:42:49.677+00:002009-09-08T02:42:49.677+00:00I think both of these two patterns attack importan...I think both of these two patterns attack important problems. EAV would save my company tens of thousands of dollars in one application alone.<br /><br />Since our databases are not relational, there is a performance cost to EAV. As with most performance comments whether this is significant depends on the details of system in question.Unknownhttps://www.blogger.com/profile/18001815881633914931noreply@blogger.comtag:blogger.com,1999:blog-7631414.post-35592781735847018272009-05-12T13:37:00.000+00:002009-05-12T13:37:00.000+00:00Trouble is, that these generic approaches must be ...Trouble is, that these generic approaches must be in some books or chapters on DB design. I've just seen data models like this implemented too often to believe it was just due to a programmer's temptation.<br /><br />Even a very well-known (and expensive) IT consulting company implemented EAV in one of my projects - now that it is referenced in thousands of lines of code, we cannot get rid of it,Uwe Küchlerhttps://www.blogger.com/profile/08199596117280621443noreply@blogger.comtag:blogger.com,1999:blog-7631414.post-34652444426392311582009-05-06T19:17:00.000+00:002009-05-06T19:17:00.000+00:00OTLT
I created a response here:
http://sholliday....OTLT<br /><br />I created a response here:<br />http://sholliday.spaces.live.com/blog/cns!A68482B9628A842A!569.entry<br /><br />One solution is to use a CHECK constraint.<br /><br />My solution is Sql Server specific, but the idea is there.<br /><br />But I have a common lookup table and a way to make sure only correct lookup values can be applied to an Entity-Column.<br />(Aka, you cannot put ingranadaCoderhttps://www.blogger.com/profile/02752822942527478679noreply@blogger.comtag:blogger.com,1999:blog-7631414.post-18485591719716129402009-03-22T12:38:00.000+00:002009-03-22T12:38:00.000+00:00Marc, what is the issue that requires a "good" EAV...Marc, what is the issue that requires a "good" EAV solution? My original contention was that generally there is no such issue, it is a figment of the developer's imagination.Tony Andrewshttps://www.blogger.com/profile/16750945985361011515noreply@blogger.comtag:blogger.com,1999:blog-7631414.post-78946693363002702002009-03-22T04:20:00.000+00:002009-03-22T04:20:00.000+00:00Can someone please discuss a *good* solution to th...Can someone please discuss a *good* solution to this issue? All we've talked about so far is how these two solutions aren't worthwhile.<BR/><BR/>What about storing the data using the EAV approach, but creating a lookup table that flattens all the fields for quick searching?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7631414.post-31919554307752908322008-06-24T11:38:00.000+00:002008-06-24T11:38:00.000+00:00I started programming and the first job I did was ...I started programming and the first job I did was databases for a small company but I moved up and now I programme the software for <A HREF="http://www.dvdplayer-reviews.co.uk/" REL="nofollow"> argos dvd player</A>s and I quite enjoy it but I never thought I would be doinmg this.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7631414.post-7466305683942963112008-05-05T09:46:00.000+00:002008-05-05T09:46:00.000+00:00I agree on the smaller project modules, even if yo...I agree on the smaller project modules, even if you set out on a lrage one by building through smaller ones bugs and errors are easier to find and remove in any application.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7631414.post-14378708257156317262008-02-15T14:03:00.000+00:002008-02-15T14:03:00.000+00:00I'm ashamed to say that I did a presentation on th...I'm ashamed to say that I did a presentation on the OTLT, and have several of these structures in my applications. The main reason to do this is to make one application to maintain the OTLT. I think that Toon Koppelaars has the right solution - no OTL Table, instead have OTL View with INSTEAD OF triggers to update the "real" lookup tables. Then write your maintenance module against the view.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7631414.post-3390319672506508512008-02-13T22:45:00.000+00:002008-02-13T22:45:00.000+00:00I would generally agree with the recommendations f...I would generally agree with the recommendations for smaller projects. However, for very large enterprise wide projects with thousands of tables, individual lookup tables pose a huge maintenance issue. Think Oracle Applications (E Business Suite). It uses the OTLT quite successfully and as far as I know, there have been no major cases of orphaned entries because the user either enters the value Rajeshhttps://www.blogger.com/profile/04766229302770546772noreply@blogger.comtag:blogger.com,1999:blog-7631414.post-35004585900759364392007-06-11T19:00:00.000+00:002007-06-11T19:00:00.000+00:00These comments have been invaluable to me as is th...These comments have been invaluable to me as is this whole site. I thank you for your comment.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7631414.post-1131693606116504992005-11-11T07:20:00.000+00:002005-11-11T07:20:00.000+00:00Clean datamodels above all other, totally agree. T...Clean datamodels above all other, totally agree. The OTLT idea does not fit in there.<BR/>And if you want to avoid having to build 33 Code-table maintain screens... Just build a Union-All view on top of the 33 code tables, add some instead-of triggers, and off you go with just one maintain-screen.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7631414.post-1127733720306669912005-09-26T11:22:00.000+00:002005-09-26T11:22:00.000+00:00In response to Lewis:Yes the alt_key approach to O...In response to Lewis:<BR/><BR/>Yes the alt_key approach to OTLT does allow DRI, however it is essentially meaningless. The FK constraint to alt_key does not define any true constraint other than "constrain this element to be *any value* in my OTLT". To constrain a field to a *partiicular type* of value stored in the OTLT - i.e. postcode, part type, whatever - which is what the FK constraint *Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7631414.post-1126798819860366402005-09-15T15:40:00.000+00:002005-09-15T15:40:00.000+00:00I agree with Tony- these are not the way to create...I agree with Tony- these are not the way to create tables. I am less of a programmer and more of a database person, mostly self-taught, and have made these errors in the past. Even if you can make the OTLT work it will create problems as the application evolves.<BR/><BR/>The concept here is that of normalization. My experience is you violate the first normal form at your own risk. There are Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-7631414.post-1115853026621465692005-05-11T23:10:00.000+00:002005-05-11T23:10:00.000+00:00Tony,I agree with the EAV. I've seen it happen an...Tony,<BR/><BR/>I agree with the EAV. I've seen it happen and have had to argue it down a few time.<BR/><BR/>The OTLT can work though. If you add an alternate unqiue key to the look up table, you can enforce uniqueness on lookup_type+lookup_code and on alt_key. Use the alt_key in your foreign key constraints. <BR/><BR/>I'm not saying everyone should rush out and implement that but it does workAnonymousnoreply@blogger.com