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
0 votes
1 answer
282 views

EAV table question

What's the best design for taxonomies in EAV tables? taxonomy id autoincrement name unique terms id autoincrement tax_id references taxonomy(id) name text unique value text ...
1 vote
2 answers
2k views

Mysql: Create a view with multiple self joins without duplicates in result

Just to clarify i can't change the tables structure, so please leave out the "you should change your tables to this and that" answers, thank you. So i have a table entities_attributes_values where an ...
0 votes
1 answer
280 views

MYSQL Multiple table optimization for advanced search

I have a users table where there is the basic information of each user: CREATE TABLE `users` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `account_type` varchar(10) NOT NULL, `email` varchar(...
0 votes
2 answers
1k views

Store parameters of various types

I am a (poor, young) scientist working to develop a database to allow a project to scale up. I am experimenting with MySQL and have had very encouraging experiences so far, but I am still novice and ...
1 vote
1 answer
1k views

Database Design - Custom Attributes / Columns

So I'm trying to figure out the best way to go about handling this database design wise. This is a rough idea of how I THINK it should go, but not really sure. If I have orders that have their own ...
1 vote
1 answer
80 views

Table filtered for multiple column: eav support table can be the best solution for search performance improvement?

I have a table with 10~15 columns (some varchar, some int, some double, and some date). Table can contain tens of millions of records. User can filter from all columns using all filter operator (equal,...
0 votes
1 answer
197 views

EAV or JSONB column for dynamic data that needs good indexing

I have some data in a Postgres database that does not fit into a static schema. There is a table "content" and each piece of content has a user-defined type. Each type can have different ...
0 votes
3 answers
663 views

Mariadb querying is slow in a table of 2.6M records with indexes

TLDR: I have a table with a lot of records and my queries are slow. I have indexes, but still the query takes a long time to execute. Need help figuring out what the problem is, or atleast a pointer ...
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....
0 votes
1 answer
999 views

Query product and product variants based on properties

I'm trying to build a filter system for an e-commerce application. The following schema is given: http://sqlfiddle.com/#!9/18b93 A product has many variants A variant as has many property_values A ...
1 vote
0 answers
2k views

Choosing between Entity-Attribute-Value or JSONB representations

I must store multiple types of documents in my Postgres database. I know the schema of each particular document type, I know what fields will be present. So, in theory, I could create a separate table ...
1 vote
1 answer
57 views

Getting ID when sorting by joined table using group also

I have small query to getting products based on variants (colors, materials), like: SELECT ANY_VALUE(id) AS id, parent_id, COUNT(id), color, material, MIN(price) AS priceMin FROM ( ...
1 vote
1 answer
160 views

Products variants in EAV

I preapre small EAV table for product variants. CREATE TABLE products ( id int auto_increment, parent_id int default null, name varchar(30) not null, PRIMARY KEY (id) ); CREATE TABLE ...
0 votes
1 answer
95 views

Products multiple variants

My query looks like: select p.id, p.name, vo.value, v.name from products p join products_variant_options pvo on pvo.product_id = p.id join variant_options vo on vo.id = pvo.variant_option_id join ...
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 ...
0 votes
2 answers
2k views

Query to find products with all the given attributes

I have a table in mySQL to store product attributes like this: ------------------------------------ | product_id | att_val1 | att_val2 | ------------------------------------ | 1001001 | x202 | ...
0 votes
1 answer
84 views

Store different data of the same format in one table, or multiple tables of the same format?

Consider I have the following MySQL tables: products id | created_at | updated_at | name | description | barcode stores id | created_at | updated_at | name | description | address product_attributes ...
0 votes
1 answer
181 views

Converting EAVT table into SCD type 2

After a lot of research and head picking, I'm still unable to find a good/clean solution to convert an entity-attribute-value-timestamp table to an scd type 2 dimension. Here's the issue: I have a CRM ...
0 votes
0 answers
87 views

Store mutiple values for same entity and attribute in EAV

Beginner to eav model,I am having an entity table,attribute table and value table. Entity Table |entity_id|entity_name| Attribute table |attribute_id|attribute_name| value table: |entity_id|...
0 votes
1 answer
446 views

optimizer uses all the wrong indexes instead of one right

This is another stupid question about optimizer. Synopsis is exactly as the subject is: the optimizer does it wrong. Seems like is has an absolute desire to use any index that contains REALM_ID field: ...
0 votes
0 answers
90 views

Is it good to join on different tables depending on discrimination column?

I want to build a MySQL database with different trading card, all belonging to different games. Each card, depending on the game, has several properties. For example : A Magic: The Gathering card has ...
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 ...
0 votes
1 answer
776 views

How To Store EAV Data When It Has Nested Objects/Entities

I have json data that have both nested objects and attributes. Eg of one json object: (After json_decode into an array:) Array ( [attributename1] => attributevalue1 [attributename2] => ...
0 votes
1 answer
181 views

Kinda EAV in SQL Server

So i need to build at database where the admin user can add and remove models without me. Instead of the normal type with all the values in one big table and them all with varchar type, I was thinking ...
0 votes
1 answer
37 views

Model types of an entity [duplicate]

Let's take a simple users table. I have a few different type of users. Let's simplify it into two, employees and customers, for the sake of the example, but keep in mind I do have much more. So I have ...
0 votes
1 answer
23 views

Large join on multiple indexed tables fails on copy of the schema but not on another

We have two schemas, both from the same template and with near identical record count (less than 100). They are both running in the same MariaDB 5.5.68. We also have existing schemas that run this ...
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 ...
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 ...
0 votes
0 answers
989 views

Flatten multiple rows from One to Many to single row

This questions is similar to what I need but the answer seems rather specific to the question rather than a general answer. One to many relationship query result in a single row and dynamic multiple ...
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 ...
0 votes
2 answers
90 views

Need help designing database, Create a table with additional columns/fields in the table, or create a new table for each different type?

So the tables that I have are Certificates, Approvers, CertificatesApprovers, Categories, Approvees, Status, AdditionalFields, AdditionalFieldsValues. Many Certificates to Many Approvers relationship. ...
1 vote
2 answers
215 views

EAV - COUNT()/SUM() on column and row values with and without NULLs

Assume that there is a table demo with these data: id userID title data 1 1 DATE 01-01-2021 2 1 COLOR RED 3 2 DATE 4 2 COLOR YELLOW 5 3 DATE 04-01-2021 6 3 COLOR RED 5 4 DATE 04-01-2021 6 4 ...
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 ...
1 vote
2 answers
981 views

Dynamic pivot table filtering and performance

I have these tables and I'm trying to rotate the subscriber to column table to horizontal and filter its result based on multiple and/or conditions like the following: WHERE first_name LIKE 'm%' AND ...
0 votes
1 answer
132 views

EAV, multiple left join query optimisation

Any assitance would be greatly welcomed. Pardon my ignorance however I am wondering if anyone else has had to encounter bad EAV performance issues. I have a particular query which takes a long time. ...
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 ...
1 vote
1 answer
114 views

Optimal mySQL design for 500k+ products with up to 50 specs

I facing a problem where I have to design a MySQL database capable of containing +500k products in approx. 1.500 categories, that can hold up to 50 specs. (like size, weight, speed, color, etc). My ...
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 ...
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 ...
0 votes
1 answer
816 views

Eav model sql select only products with all 3 values?

I have an EAV (Entity Attribute Value) based table for smartphones like the following: What if the user wants to see all phones with - red color - steel material The query is like: WHERE (...
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, ...
0 votes
0 answers
874 views

Is it practical to use entity-attribute-value (EAV) model to support custom user fields and avoid bloating of tables?

I am currently working on a medium-sized web app project that is already in production using Laravel framework and MySQL 5.6.40. My clients have been asking for the ability to add additional ...
-1 votes
1 answer
63 views

How can I select non null columns from a view of EAV tables

I have a VIEW that queries my E-A-V model which uses datatype tables to store the values. I have an entity that contains many attributes, each attribute can be a different datatype. The DataType ...
1 vote
0 answers
160 views

E-A-V entity Attribute Value Generic Model with Min Max Values

I have an entity consisting of several attributes (can be added, can be removed from entity - and new attributes made as well). I need to be able to store the values - so for example I have a list of ...
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 ...
0 votes
1 answer
53 views

How is it possible to model in a database many different products?

For example, a smartphone might have attributes such as RAM and camera resolution, whereas a fridge has way different attributes. Is there a way to store these data in a relational database?
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 ...
-1 votes
2 answers
141 views

Designing a schema for an entity with 40 attributes

I am having a doubt on designing a schema for my current project in MySQL. It is mainly because there is an entity that I made and has 40 attributes. The table/entity X contain of fields like: ...
0 votes
1 answer
34 views

Using rows from one table as fields in SELECT

I have three tables in my MySQL database - company, extra_fields and extra_fields_info: mysql> describe company; +-----------------------------+------------------+------+-----+---------+-------+ | ...
1 vote
0 answers
116 views

Is EAV a good choice for scenario when the number of fields may differ client to client?

Suppose that you are developing an application that could be used by multiple clients. You have to store "basic details" of an employee of a company (your client) into your database. The first 15 ...