Dumb Vocab Question about What This Type of Table is Called

Question

Dumb Vocab Question about What This Type of Table is Called

lek103

SSC-Addicted

Points: 432
More actions
October 15, 2013 at 9:30 am

#283895

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:
FieldTypeIDFieldTypeDescription
1State
2Animal
3Vegetable
FieldTypeIDFieldValueIDFieldValue
11Massachusetts
12Vermont
23Cat
24Frog
35Broccoli
36Radish
...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?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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, MVP, M.Sc (Comp Sci)
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

lek103 SSC-Addicted Points: 432 More actions · Answer 3

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!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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, MVP, M.Sc (Comp Sci)
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

lek103 SSC-Addicted Points: 432 More actions · Answer 5

Thanks! Those articles are full of stuff that somehow never occurred to me. :blink:

aaron.reese SSChampion Points: 13498 More actions · Answer 6

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.

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 7

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)