Skip to main content
OverflowAI is here! AI power for your Stack Overflow for Teams knowledge community. Learn more

Questions tagged [eav]

Entity-Attribute-Value. A data model with a potentially big number of attributes of which only few apply to a single entity.

Filter by
Sorted by
Tagged with
74 votes
5 answers
15k views

Is there a name for this database schema of key values?

We process a routine data feed from a client who just refactored their database from a form that seems familiar (one row per entity, one column per attribute) to one that seems unfamiliar to me (one ...
prototype's user avatar
  • 853
30 votes
5 answers
166k views

How to optimize very slow SELECT with LEFT JOINs over big tables

I was googling, self-educating & looking for solution for hours but with no luck. I found a few similar questions here but not this case. My tables: persons (~10M rows) attributes (location, age,...
Martin's user avatar
  • 411
19 votes
3 answers
79k views

Schema design for products with multiple variants/attributes?

I'm using MySQL. The idea is similar to shopify with a different concept, so users are going to add their own products with multiple types of variants and attributes. From all the research I've done ...
lesandru's user avatar
  • 321
15 votes
4 answers
12k views

Proper way to store a value that could be multiple different types

I have an Answers table and a Questions table. The Answers table has a value, but depending on the question, this value could be a bit, nvarchar, or number (so far). The Question has a notion of what ...
David Garrison's user avatar
13 votes
2 answers
10k views

Alternative to EAV for dynamic fields in a star schema data warehouse

I need to support dynamic fields and values in a big datawarehouse for storing API requests log, my user case is that I need to store all API requests query string and able to perform query against ...
Howard's user avatar
  • 249
12 votes
3 answers
22k views

Inventory database structure when inventory items have varying attributes

I'm building an inventory database to store enterprise hardware information. The devices the database keeps track of range from workstations, laptops, switches, routers, mobile phones, etc. I'm using ...
TheSecretSquad's user avatar
7 votes
2 answers
10k views

Efficient query for an advanced EAV model

I'm kind of stuck on how to build an effecient query which returns the data of the following EAV structure. Today, a product table already exists, containing 4 fixed fields. We want to upgrade the ...
Zeep's user avatar
  • 95
6 votes
1 answer
2k views

PostgreSQL versus MySQL for EAV structures storage

Is there any practical difference or what are the advantages of using Postgres 9.x versus MySQL 5.x for storing EAV (Entity-Attribute-Value) structures, especially in terms of read performance?
Alex's user avatar
  • 163
6 votes
2 answers
3k views

Which is better for long term database scalability: adding columns or having key/value store

For application, which has few (5-10) settings at the moment but will have a lot more (up to 100) in the future which would be better approach: to add new columns to settings table every time new ...
valentinas's user avatar
6 votes
1 answer
3k views

A good use case for Postgres JSONB?

I am looking to create an Asset Management System to track assets (laptops/desktops, monitors, keyboards, mice, bags, software keys, etc), basically anything that can be assigned to an employee. My ...
greyfox's user avatar
  • 161
6 votes
1 answer
3k views

Postgres 9.4+: JSONB instead of EAV

I'm figuring out a way to structure a new PostgreSQL database, and I'm curious if I could use a JSONB column instead of the EAV model for storing properties of an entity. This seems like a flexible ...
coussej's user avatar
  • 203
6 votes
1 answer
1k views

Design for scientific data. Data table with hundreds of columns or data table with a generic value column and hundreds of rows (EAV)?

I am trying to improve the data storage in biomedical science lab where I recently started working. The existing workflow is atrocious, involving many differently formatted Excel sheets that all get ...
mrestko's user avatar
  • 63
6 votes
1 answer
466 views

Why is an additional filtered statistic being ignored (EAV schema)?

I'm trying to improve a row estimate for this sub-query (of a larger query). The estimate is showing 1266 rows. The actual is 117k rows. This particular property (EAV schema) only has two values ...
crokusek's user avatar
  • 2,120
6 votes
1 answer
3k views

EAV structure explained in Layman's terms

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 ...
JustBeingHelpful's user avatar
6 votes
1 answer
144 views

Can I do a conditional JOIN or conditional SELECT based on the data in a table?

I think I have a problem where EAV is the best choice. I am planning my tables as follows. motherboard motherboard_id | model_name int | varchar Example: motherboard_id | model_name ---...
Lord Loh.'s user avatar
  • 163
5 votes
2 answers
1k views

Solutions for reporting off of an EAV structured database?

Existing setup SQL Server 2005 with standard EAV table that users want to slice and dice with a BI tool (Cognos). Is there any hope that we can transform this data into a format that we can report ...
IronicMuffin's user avatar
5 votes
3 answers
4k views

Optimizing query using view on EAV structure

An application is writing into a database that follows an EAV structure, similar to this: CREATE TABLE item ( id INTEGER PRIMARY KEY, description TEXT ); CREATE TABLE item_attr ( item ...
Bruno's user avatar
  • 1,555
5 votes
1 answer
4k views

Many to many between lots of tables?

TL;DR: How could I set up many-to-many relationship between that many tables and is it viable/recommended? Each body type has its own table Each car in each body type table can has several features ...
Oscar's user avatar
  • 153
5 votes
1 answer
872 views

Help with "Flexible" vs Fixed Tables

We are designing a database in SQL Server to handle sales commissions. A diagram of the current schema looks like this: There are only a couple of us on the project, and our boss dropped this design ...
Zairja's user avatar
  • 215
4 votes
3 answers
294 views

Does separating VARCHAR values based on size have a performance gain?

I am working with a team that is trying to implement an EAV system. They have decided to split the attribute-value tables out by type and they are debating using different tables for different size ...
Justin Obney's user avatar
4 votes
2 answers
514 views

Is EAV an appropriate design for a collection of coexisting metadata-rich "fact estimates" on objects? [closed]

I'm attempting to architect a system that will collect estimates on various quantities (10+) on each of many (300K+) objects, and make decisions from the historical record of these estimates. For ...
btown's user avatar
  • 141
4 votes
3 answers
263 views

What normalization rules does this table break

Our previous DBA got fed up with the development teams frequent requests to change the database schema to add and delete columns. He then advised to the developers that he would create simple tables ...
Rahul Khimasia's user avatar
4 votes
3 answers
227 views

Some users can have a fixed amount of additional fields, EAV or not?

Say I have to store data about two different types of person roles or occupations, i.e., Consumers and Merchants, and both types of roles share a single base table called User. In this respect, ...
JC Lee's user avatar
  • 205
3 votes
2 answers
293 views

EAV-like table with both primary column pointing to the same foreign column

Background: I'm a programmer who, while had to work with simple databases several times, never had to work with complex ones. I.e. I'm proficient with basic stuff, but clueless about advanced features ...
o0'.'s user avatar
  • 133
3 votes
2 answers
99 views

Using table values as column

I have 3 tables: Products, containing - id, name and description Attributes, containing - id, name prod_attri_rel, containing - id, prod_id, attri_id and value Products looks like: 1 | test | just ...
Magic-Mouse's user avatar
3 votes
1 answer
389 views

What's the best way to store and search through many product specifications

Let's say I have a lot of products and all of those products have a lot of specifications. These specifications can change at any time (e.g. "colour" could suddenly be available for new products) and ...
user77026's user avatar
3 votes
3 answers
1k views

Field types database design best practice

I am creating a cms system where the user can create fields to use in the templates of the website. I want to create different types like 'string', 'html', 'integer' etc. The user must also have the ...
Jerodev's user avatar
  • 131
3 votes
1 answer
193 views

Need help in picking solution and understanding when to add columns vs EAV

I've been working on the following problem for a long time and I can't decide between two solutions. While both would work to solve the problem, neither "feels" exactly right to me. Any advice on ...
Richard's user avatar
  • 133
3 votes
1 answer
5k views

MySQL Best approach for recording last update timestamp for every column and every row

This question is about possible approaches for time stamping every column of every row in a table. I have a table with a small number of rows (fewer than 200 and not likely to grow much larger). Each ...
pbarney's user avatar
  • 171
3 votes
0 answers
100 views

Optimality of Columns vs. Complementary Table in MySQL

I'm starting work on a project where I'll have a table of entries as well as a table of descriptors. The entries will be updated infrequently, if ever, and the descriptors table will be largely ...
Cameron Sumpter's user avatar
2 votes
1 answer
4k views

Alternatives to EAV model

I am looking for a design pattern that would allow me to capture metadata relating to columns on a table. More specifically, here is my situation. I have a database with fields in a field table, ...
Antoine Viscardi's user avatar
2 votes
3 answers
7k views

How to store multiple data types for same column (for many columns)

To preface, similar questions have been asked, but I have not found any that address a table that has a large number of columns affected by this issue. All the similar seem to address a table that ...
Arya's user avatar
  • 134
2 votes
3 answers
265 views

Schema-less data in a database?

I'm wondering if there's any database technology where its structure or schema does not need to be defined upfront? For example it may start off with just one column or row and the user will keep ...
Jeno's user avatar
  • 21
2 votes
1 answer
1k views

MySQL - Is it good to have millions of tables in a single database?

I have designed new MySQL database for my project, In my project I need to create tables dynamically with dynamic columns. So I want to know is their any impact on database performance if I have ...
Shri's user avatar
  • 145
2 votes
2 answers
9k views

How to convert rows into columns and query on them in Mysql?

I have three tables Patients, which contains the name of my patients, Controls which represents the UI controls which could be drawn for each illness , and ControlsValues table which contains the ...
rabar kareem's user avatar
2 votes
1 answer
72 views

Database design suggestion for improving existing design

Background I am working on an IoT application that deals with sensors, their configurations and their readings. Following are the requirements of the system, Each sensor can support multiple features....
Hem Bhagat's user avatar
2 votes
1 answer
3k views

What is the proper way of designing database with dynamic fields in MySQL + PHP?

I need help on designing a proper structure for a database table with dynamic fields. I am using MySql with PHP and the system I'm currently working on (inventory system) requires a table that accepts ...
Jan Roenz Gaynilo's user avatar
2 votes
1 answer
462 views

best way to add Entity id & entity types in database

we are doing shopping site with mysql with eav model to store values. in below image , 2 types of entity types are there under products_information table : 1) Mobile 2) T-shirts I wanted to know in ...
Baby in Database's user avatar
2 votes
1 answer
4k views

Multi-variance products

My current system houses different variants of Products in different tables; Productgroups Productcolors Productsizes Productmodels Products can reside under 1 Productgroup and can have multi-...
User2910293's user avatar
2 votes
0 answers
133 views

EAV with very few of each entity

I have data that fits an entity-attribute-value structure but with very few entries (same attributes but different values) per entity. The main characterizations of my data are as follows: ~700 ...
user3747260's user avatar
2 votes
0 answers
424 views

Migrating schema from Entity-Attribute-Value (EAV) model to proper tables

I have an Entity-Attribute-Value (EAV) database in Oracle 11. For many, many reasons, I would like to model this using actual tables. Changing all the client code at once is not possible. Existing ...
WW.'s user avatar
  • 131
1 vote
5 answers
2k views

Should I have a column for every data type or 1 column that stores everything

I have a database table that currently looks like this: PageField -Id (Int, PK) -FieldType (String) Can be Text, Decimal, Integer or Bit -Value (This stores the value regardless of the FieldType, so ...
user1786107's user avatar
1 vote
1 answer
388 views

Disadvantage of Changing database schema - Insert or delete new columns/tables in middle

As a read in lot of links , for e-commerce shopping sites, as it may require to add lot of attributes in regular basis, is it okay if we go with Flat tables ? Whats the disadvantage of changing ...
Baby in Database's user avatar
1 vote
2 answers
249 views

INSERT or UPDATE the values from a table A into the columns of table B based on table A's value in its row?

I have a table which contains attribute names with there values but each row is a attribute with its value, ItemId ItemAttributeName ItemAtributeValue 405 Application High AmpacityConnections 405 ...
zerodoc's user avatar
  • 235
1 vote
1 answer
242 views

Adding LIMIT changes PostgreSQL plan from good to a bad one

I have a query on a typical EAV schema: SELECT contacts.id FROM contacts LEFT OUTER JOIN (SELECT DISTINCT contacts_values.company_id AS company_id, contacts_values.id AS id ...
rmihael's user avatar
  • 121
1 vote
1 answer
140 views

Are lots of columns or joins better in Django

I am writing a simple Django app that stores and displays user objects with lots of attributes. The user may have up to three hundred attributes. These attributes however, can be grouped off. For ...
user2288983's user avatar
1 vote
1 answer
2k views

Horizontal schema vs Vertical schema

I am but a modest SQL DBA. I would like to request your advice on the design of the central part of our new proposed database. Brent Ozar had something to say about this: Building A SQL table ...
lpcoolee10's user avatar
1 vote
1 answer
833 views

To EAV or not to EAV?

I am working on an application which records data from different forms. New forms can (and will be) constructed dynamically by an admin. I am wondering what would be the correct way to save this data ...
Bart's user avatar
  • 113
1 vote
1 answer
904 views

Does PostgreSQL support wildcard indexes on JSON?

I have a multi-tenant service with a table like this: project_id | user_id | user_properties Each project belongs to a different customer and customers can freely attach metadata to their users. A ...
collimarco's user avatar
1 vote
1 answer
816 views

EAV with multiple values per attribute

I am working on e-commerce website with wide range of categories (TV, mobile phones, furniture, etc). Since there is such large amount of attributes I decided to use EAV model to store attributes and ...
5w0rdf1sh's user avatar
  • 218