BillyJack (1/21/2013)
I think Eric's example demonstrates the strength of an EAV design..you can add new attributes via data and not table structure.David, I see from the responses that your article on this topic has initiated some lively debate! Well done!
Depending on how we look at it, the Customer_Contact table I presented above is containing only one attribute (contacts). It's just flexible enough to accomodate multiple types of contacts.
"Write a query that returns customer's name, primary method of contact, and last date/time of contact for all customers who have a late payment past 30 days."
Given the above requirement, I don't want phone numbers, emails, and faxes all stored in seperate tables. Also, if dealing with seperate tables, I especially don't want a requirement from end users to start supporting text messaging.
For the majority of contact types, a single table need contain only a type code and a single alpha-numeric value. The application or user can then decide what to do with the information. They just need to know the contact type they're dealing with for context.
Of course mailing and home address should almost certainly be in a seperate table, because it is composed of multiple attributes (city, zip, geo location, etc.) that are commonly queried seperately.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho