Questions tagged [entity-attribute-value]

A method of modelling and storing data by breaking it into three parts: the entities, their attributes and their values.

The basic idea is to store attributes, and their corresponding values, as rows in a single table.

Typically the table has at least three columns: entity, attribute, and value. Though if there is only a single relevant entity, e.g. a table for application configuration or option settings, the entity column can be excluded.

Entity attribute value modeling (also known as EAV) is an alternative to 'row modeling', i.e. storing all entity values for the same attribute in the same table column.

Contrasted with a traditional relational data model, it permits variable associations of attributes with entities.

873 questions
142
votes
10 answers

Entity Attribute Value Database vs. strict Relational Model Ecommerce

It is safe to say that the EAV/CR database model is bad. That said, Question: What database model, technique, or pattern should be used to deal with "classes" of attributes describing e-commerce products which can be changed at run time? In a…
Zachary Scott
  • 20,968
  • 35
  • 123
  • 205
92
votes
1 answer

Modeling Product Variants

I've been trying to model product variants and thought that I might need to use EAV. I might have been able to do it without EAV, but I'm concerned that I might have missed something. Here's my design: Here's what I am trying to represent: A…
78
votes
6 answers

Magento - Retrieve products with a specific attribute value

In my block code I am trying to programmatically retrieve a list of products that have a attribute with a specific value. Alternately if that is not possible how would one retrieve all products then filter them to just list the products with a…
Christian
  • 1,272
  • 3
  • 13
  • 15
55
votes
3 answers

ALTER TABLE in Magento setup script without using SQL

Jonathon Day says "updates SHOULD NOT be in the form of SQL commands". I haven't come across any DDL or DML statments that cannot be executed via Magento's config structures. (In the question How can I migrate configuration changes from…
clockworkgeek
  • 37,650
  • 9
  • 89
  • 127
54
votes
5 answers

Alternatives to Entity-Attribute-Value (EAV)?

Our database is designed based on EAV (Entity-Attribute-Value) model. Those who have worked with EAV models know all the crap that comes with for the purpose of flexibility. I asked my client about the reasons why using EAV model (flexibility), and…
36
votes
4 answers

Database EAV Pros/Cons and Alternatives

I have been looking for a database solution to allow user defined fields and values (allowing an unlimited number). At first glance, EAV seemed like the right fit, but after some reading I am not sure anymore. What are the pros and cons of EAV? Is…
Nic Hubbard
  • 41,587
  • 63
  • 251
  • 412
32
votes
6 answers

Designing a SQL schema for a combination of many-to-many relationship (variations of products)

I hope the title is somewhat helpful. I'm using MySQL as my database I am building a database of products and am not sure how to handle storing prices/SKU of variations of a product. A product may have unlimited variations, and each variation…
Zaki Aziz
  • 3,592
  • 11
  • 43
  • 61
30
votes
1 answer

Table Module vs. Domain Model

I asked about Choosing a method to store user profiles the other day and received an interesting response from David Thomas Garcia suggesting I use the Table Module design pattern. It looks like this is probably the direction I want to take.…
30
votes
3 answers

Should I use EAV model?

I'm am designing my database/domain for an eCommerce application and I'm having a hard time figuring out how to store products. The website will sell a wide range of products, pens, thongs, tattoos, umbrellas, everything. Each of these product will…
Cobby
  • 5,273
  • 4
  • 28
  • 41
29
votes
2 answers

How can I handle different data types in an Entity-Attribute-Value design (e.g. single table with multiple columns or multiple tables per data type)?

I want to create a patient/sample metadata table using an entity-attribute-value (EAV) approach. Question: How should I handle the varying column type of the value (e.g. string, numeric, or foreign key to dictionary table) based on the…
lebolo
  • 2,120
  • 4
  • 29
  • 44
29
votes
3 answers

Entity-Attribute-Value Table Design

I am currently designing a database structure for the products section of an ecommerce platform. It needs to be designed in such a way that makes it possible to sell an infinite number of different types of products with an infinite number of…
28
votes
7 answers

How to pivot a MySQL entity-attribute-value schema

I need to design tables which stores all the metadata of files (i.e., file name, author, title, date created), and custom metadata (which has been added to files by users, e.g. CustUseBy, CustSendBy). The number of custom metadata fields cannot be…
Ashok
23
votes
1 answer

How to implement a catalogue for meta-data and automating SQL in a database?

I have read here the discussions on 5NF, EAV and 6NF and the need for a catalogue to handle meta-data and the complex SQL "automatically". How is that implemented in practice? PerformanceDBA wrote several answers on 6NF and EAV that mentions…
21
votes
3 answers

Is there a Entity Attribute Value (EAV) framework out there for PHP/MySQL?

Is there a Entity Attribute Value framework out there for PHP/MySQL? I'm starting to write my own, but I feel like its been done already. Any suggestions?
Eric Guerin
19
votes
5 answers

MySQL correlated subquery in JOIN syntax

I would like to provide a WHERE condition on an inner query by specifying innertable.id = outertable.id. However, MySQL (5.0.45) reports "Unknown column 'outertable.id' in 'where clause'". Is this type of query possible? The inner query is pivoting…
Taylor Gerring
  • 1,825
  • 1
  • 12
  • 17
1
2 3
58 59