Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dumb Vocab Question about What This Type of Table is Called Expand / Collapse
Author
Message
Posted Tuesday, October 15, 2013 9:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 5, 2014 5:48 PM
Points: 35, Visits: 104
This is probably a dumb question, but I'm totally self-taught and have learned everything I know from the Internet, so sometimes I struggle with knowing the correct terms to use for things. I've tried Googling this and I can't seem to adequately describe it to get good search results. So if I have the following 2 tables:

FieldType
FieldTypeID
FieldTypeDescription

FieldValueList
FieldTypeID
FieldValueID
FieldValue

...and they're populated like this:

FieldTypeID FieldTypeDescription
1 State
2 Animal
3 Vegetable

FieldTypeID FieldValueID FieldValue
1 1 Massachusetts
1 2 Vermont
2 3 Cat
2 4 Frog
3 5 Broccoli
3 6 Radish

...is there terminology for the FieldValueList table? Basically a table that's a dumping ground for a bunch of lists of things that would appear in drop-downs based on their FieldTypeID?
Post #1504823
Posted Tuesday, October 15, 2013 9:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:58 AM
Points: 5,369, Visits: 9,890
Those are horrible. I recommend avoiding that design if at all possible. I've seen it described as EAV (Entity Attribute Value), OTLT (One True Lookup Table) and MUCK (Massively Unified Code Key).

John
Post #1504828
Posted Tuesday, October 15, 2013 9:58 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:19 AM
Points: 43,002, Visits: 36,158
The One True Lookup Table design. Also known to people who have worked with them as 'Oh $%$$%$$ not that thing again'

It's a 'design' that looks so attractive to people who haven't worked with it but it's an absolute mess, causes all sorts of integrity issues, performance issues and to be honest should be avoided for most scenarios. There's very, very little benefit to that design and a whole lot of problems



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1504836
Posted Tuesday, October 15, 2013 10:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 5, 2014 5:48 PM
Points: 35, Visits: 104
Ah, I see! Thanks to you both! I wasn't aware that it was considered poor design. I've always used that approach for any list items about which I know I'll never need to store any other information, such as name prefixes and suffixes, states, etc. I'll do some research on OTLT and perhaps reconsider my approach. Thank you!
Post #1504863
Posted Tuesday, October 15, 2013 10:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:19 AM
Points: 43,002, Visits: 36,158
http://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-big-design-mistakes.html
https://www.simple-talk.com/sql/t-sql-programming/look-up-tables-in-sql-/



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1504867
Posted Tuesday, October 15, 2013 1:01 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 5, 2014 5:48 PM
Points: 35, Visits: 104
Thanks! Those articles are full of stuff that somehow never occurred to me.
Post #1504922
Posted Wednesday, December 11, 2013 10:33 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
Front end developers love EAV tables because they can quickly built an interface that allows users to configure the properties of an entity (think a website that sells books and wine. One needs ISBN number, author, publisher, the other needs Vintage, Terroire, Grape, Acidity etc..)

But reporting on it is a nightmare as you effectively have to unpivot the data to use it and create very wide sparsely populated tables anyway and you have no idea how many columns you are going to need.



Post #1521982
Posted Wednesday, December 11, 2013 4:25 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:48 AM
Points: 37,075, Visits: 31,631
As with all else, "It Depends". Please see the latest article from Tony on the subject.
http://www.sqlservercentral.com/articles/Editorial/105414/

I agree that they are a panacea for nothing and usually cause a whole lot of trouble especially for those who might not be well practiced at them. But, they are a tool that can be used very successfully for certain things.

I also wouldn't necessarily call the tables given by the OP EAV or OTLT any more than I'd call a check account and EAB or OTLT. "IT DEPENDS!"


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1522093
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse