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.
122
questions
0
votes
0
answers
100
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
47
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
(
...
- 123
1
vote
1
answer
61
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 ...
- 123
0
votes
1
answer
47
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 ...
- 123
0
votes
1
answer
25
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
...
- 101
0
votes
0
answers
50
views
Sorting not working in mysql stored procedure
The procedure works, but sorting of dynamic columns does not work, according to their data type (each column has its own data type: CHAR, SIGNED, DECIMAL...). Column names are created dynamically &...
- 1
0
votes
0
answers
43
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|...
- 1
0
votes
0
answers
44
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 ...
- 101
0
votes
1
answer
97
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
1
answer
130
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
35
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 ...
- 123
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 ...
- 101
1
vote
2
answers
68
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
...
- 235
0
votes
0
answers
435
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 ...
- 23
0
votes
2
answers
69
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.
...
- 3
2
votes
3
answers
4k
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 ...
- 134
1
vote
2
answers
175
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
...
- 13
1
vote
1
answer
538
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 ...
- 513
1
vote
2
answers
676
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 ...
- 113
2
votes
1
answer
3k
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 ...
- 161
1
vote
1
answer
108
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 ...
- 11
0
votes
1
answer
614
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 (...
0
votes
1
answer
81
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. ...
0
votes
1
answer
243
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: ...
- 208
2
votes
1
answer
3k
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
671
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 ...
- 101
-1
votes
1
answer
51
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 ...
- 167
1
vote
0
answers
136
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 ...
- 167
0
votes
1
answer
46
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?
- 101
1
vote
1
answer
177
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
...
- 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;
+-----------------------------+------------------+------+-----+---------+-------+
| ...
- 83
1
vote
0
answers
99
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 ...
- 111
0
votes
1
answer
105
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 ...
- 141
-1
votes
2
answers
860
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 ...
- 1
3
votes
1
answer
140
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 ...
- 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 ...
- 103
0
votes
1
answer
71
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 ...
- 33
0
votes
0
answers
37
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 ...
- 161
0
votes
1
answer
565
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] => ...
- 121
0
votes
1
answer
67
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?
0
votes
2
answers
66
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 ...
- 1
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,
...
- 33
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 ...
- 11
1
vote
1
answer
31
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| example@example.com
fields
| field |value
| --- |---
| parentId |123 -> users.id
| ...
- 111
0
votes
1
answer
37
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,
...
- 1,069
0
votes
1
answer
927
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 ...
- 13
0
votes
1
answer
788
views
Query polymorphic relation table
I'm trying to query the following table:
+----+------------------+----------------+-------------+-------+
| id | describable_type | describable_id | property_id | value |
+----+------------------+----...
- 13
1
vote
1
answer
994
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 ...
- 133
0
votes
1
answer
238
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(...
- 1
0
votes
1
answer
939
views
PRODUCT_ID as primary key rather then the UPC(UNIVERSAL PRODUCT CODE) [closed]
I have a very strange kind of question in my mind, I'm using the EAV model in my E-COMMERCE DB. I have a product table with primary key p_id which is auto_increment, and to save its attributes I have ...
- 103