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
92 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 ...
Fabian's user avatar
  • 103
1 vote
1 answer
72 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,...
Claudio's user avatar
  • 11
0 votes
3 answers
391 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 ...
Ajith Kumar's user avatar
2 votes
1 answer
69 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
1 vote
0 answers
1k 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 ...
Daniel Díaz Carrete's user avatar
1 vote
1 answer
56 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 ( ...
kicaj's user avatar
  • 123
1 vote
1 answer
152 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 ...
kicaj's user avatar
  • 123
0 votes
1 answer
89 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 ...
kicaj's user avatar
  • 123
0 votes
1 answer
78 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 ...
user2190492's user avatar
0 votes
0 answers
82 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|...
Atchaya's user avatar
0 votes
0 answers
80 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 ...
Altherius's user avatar
  • 101
0 votes
1 answer
166 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 ...
Rami Ouanes's user avatar
0 votes
1 answer
175 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 ...
Anton Christiansen's user avatar
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 ...
Jeremy Belolo's user avatar
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 ...
David Hamilton's user avatar
1 vote
2 answers
220 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
0 votes
0 answers
879 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 ...
Marc-9's user avatar
  • 23
0 votes
2 answers
86 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. ...
12johnny's user avatar
2 votes
3 answers
6k 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
1 vote
2 answers
214 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 ...
Drahcir's user avatar
  • 13
1 vote
1 answer
854 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
2 answers
948 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 ...
Lara's user avatar
  • 113
2 votes
1 answer
4k 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
  • 161
1 vote
1 answer
113 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 ...
RePsEj's user avatar
  • 11
0 votes
1 answer
779 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 (...
user208176's user avatar
0 votes
1 answer
127 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. ...
funnypeople's user avatar
0 votes
1 answer
416 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: ...
drookie's user avatar
  • 228
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
0 votes
0 answers
851 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 ...
Helioarch's user avatar
  • 101
-1 votes
1 answer
62 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 ...
StixO's user avatar
  • 167
1 vote
0 answers
155 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 ...
StixO's user avatar
  • 167
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?
theodosis's user avatar
  • 101
1 vote
1 answer
235 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
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; +-----------------------------+------------------+------+-----+---------+-------+ | ...
turbonerd's user avatar
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 ...
SamuraiJack's user avatar
0 votes
1 answer
123 views

How to store a bunch of columns and where you don't know how many there will be, the columns change, and new columns are introduced

So the so source of the data is the internet, and random bots that submit values to my apache server. Periodically new columns appear, but they are not always present. The other problem is then you ...
cybernard's user avatar
  • 141
-1 votes
2 answers
1k views

EAV in postgresql

I'm building an application that needs to be deployed several times, with a relatively well organized data model. A table needs to be configured for each deployment with a small number of additional ...
phme's user avatar
  • 1
3 votes
1 answer
183 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
0 votes
1 answer
2k views

Modelling products along with their attributes and discounts for an e-Commerce database

Scenario My application will have products belonging to 1 company. Each of those products will: Have at least 1 quantity Have 0 or more discounts Have 1 or more attributes Belong to 1 category, which ...
JeremyVm's user avatar
  • 103
0 votes
1 answer
90 views

How to find an entry from filtering with two associated tables

This questions has spinned off of another question I probably did not ask correctly from the get go. I have some tables, where attributes and attributes_entries that hold a pair of dynamic values for ...
gstlouis's user avatar
0 votes
0 answers
42 views

MySQL Query to Display Organized by Date

I'm good with basic types of queries but this has me stumped. I have the table schema listed below, I need display the results in a table, with each row displaying the available results for a given ...
Shane Da Silva's user avatar
0 votes
1 answer
747 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] => ...
forgodsakehold's user avatar
0 votes
1 answer
86 views

Is it Possible to have a database table table of morethan 3000 fields [closed]

I have a form of more than 3000 fields and i want to create a table without splitting it into two. Is it okay? Or it will have problems?
Achoda james's user avatar
0 votes
2 answers
127 views

I would like to get a unique based on email address (MySQL)

I can work on basic queries and have been having a bit of trouble the way Wordpress stores their database information. The below query I have been using to download all form submissions for contests ...
kaykills's user avatar
0 votes
1 answer
53 views

pulling multiple entries from a match of two tables

I have two tables, attributes and attributes_entries attributes hold the title and attribute_entries hold the value of that title. if I do: SELECT attribute_entries.title AS ent_title, ...
gstlouis's user avatar
1 vote
1 answer
2k views

MYSQL Select data from two different tables BUT represent the data in multiple rows

I am needing to select all email addresses from two different tables with a single query. The goal of this is to list out all email addresses a client has per row. Both tables are structured ...
megabyte's user avatar
1 vote
1 answer
32 views

SQL to merge related documents into 1 query row (MySQL)

I'm working with the following data structure: users | field| value | --- | --- | id | 123 | email| [email protected] fields | field |value | --- |--- | parentId |123 -> users.id | ...
Tom Maitland's user avatar
0 votes
1 answer
41 views

Return proper column based on other column's values

Consider an EAV table with separate columns for each datatype: CREATE TABLE eav_values ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, entity_id INT NOT NULL, name VARCHAR(127) NOT NULL, ...
dotancohen's user avatar
  • 1,065
0 votes
1 answer
985 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 ...
rqk991's user avatar
  • 13
0 votes
1 answer
953 views

Query polymorphic relation table

I'm trying to query the following table: +----+------------------+----------------+-------------+-------+ | id | describable_type | describable_id | property_id | value | +----+------------------+----...
rqk991's user avatar
  • 13