EAV structure explained in Layman’s terms

database-designeavontology

I've read several articles online that explain how EAV structures work, but there are different interpretations of them. Some articles mention that it's a representation of a data model metadata (or schema). Other articles describe an EAV structure as being an ontology. I picture an ontology as either a conceptual model (showing relationships among "things" in the world), or class instances to the actual data in a database.

Can someone explain what each table in this model represents in Layman's Terms and how one would use this EAV data model?

I didn't see this as a representation of a data model, because [Object] is not directly linked to [Attribute]. You need an actual value to relate those two. So there's nothing to enforce that you could use a [Attribute] A with [Object] O if A is a field/column of table O in your data model. Now I did see the RI tables. I assumed that meant "Referential Integrity". But if we're talking about ontologies, then we could call it relationship identity. I'm merely referring to the relationship or predicate when talking about an ontology triple–meaning Subject/Predicate/Object. Note, that the joins are not connected with attribute, which would represent more of a relational data model FK/PK relationship with table columns, so I expected it to be connected to Attribute, not Object. It's connected with [Object].

Please shed some light on this if you're able. Maybe I'm thinking too much into this. If you have a different EAV model that's useful, let me know.

Reference: http://eav.codeplex.com

enter image description here

Best Answer

Here is what I think is intended in this model:

  • Category is a kind of thing. In a data model it would be an entity type, in a database it would be a table.
  • Attribute is a facet of a thing. In a data model it would be a predicate type, in a database it would be a column.
  • Object is an instance of a thing. In a data model it would be an entity, in a database it would be a row (in a particular table).
  • Value is an instance of a predicate or a column value in a database table.
  • Domain Lookup is a mechanism that allows you to constrain attribute values. This implementation works by constraining the value to one in a set of legal values. In a database this could be likened to a check constraint or a lookup table. Obviously there are other types of constraints that could be imposed (but aren't in this metadata model) such as range constraints.
  • RIConfig is a relational constraint between two kinds of things. This is a relationship type in a data model or a foreign key constraint in a database. At this level it is describing the relational rule, not the instances of relationships.
  • RI is an instance of a relationship between to specific entities. In a physical database, this would be a record in a foreign key index (perhaps?)

You are quite right to observe that the modeling of relationships is flawed insofar as it doesn't account for the implementation of these relationships. RIConfig should join to Attribute not to Category - or rather, there should be an intersection table between RIConfig and Attribute to allow for compound primary/foreign key relationships.

As I noted above, domain constraints are also not handled with realistic flexibility.

Given the limitations of this model, it would not be adequate as a metadata repository for many real-world databases.

Related Question