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
1
answer
251
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(...
CommunityBot
- 1
0
votes
1
answer
938
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 ...
CommunityBot
- 1
0
votes
1
answer
257
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
...
CommunityBot
- 1
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 ...
CommunityBot
- 1
0
votes
0
answers
313
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
51
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
73
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 ...
- 31.8k
0
votes
1
answer
55
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 ...
- 74.8k
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 ...
CommunityBot
- 1
6
votes
1
answer
462
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 ...
- 2,181
0
votes
2
answers
1k
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 | ...
- 1,795
0
votes
1
answer
31
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
...
- 24.4k
0
votes
1
answer
114
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 ...
- 11
0
votes
0
answers
83
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 &...
- 74.8k
0
votes
0
answers
56
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|...
- 18.9k
0
votes
1
answer
269
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: ...
CommunityBot
- 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 ...
- 74.8k
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 ...
0
votes
1
answer
607
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] => ...
CommunityBot
- 1
0
votes
1
answer
139
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 ...
- 12.5k
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 ...
- 74.8k
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 ...
- 74.8k
18
votes
3
answers
71k
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
84
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
...
- 69.1k
0
votes
0
answers
532
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.8k
74
votes
5
answers
14k
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 ...
- 167k
0
votes
2
answers
72
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.
...
- 31.8k
1
vote
2
answers
188
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
...
- 28.9k
1
vote
1
answer
619
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 ...
- 42.5k
1
vote
2
answers
730
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 ...
- 74.8k
0
votes
1
answer
93
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. ...
- 74.8k
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 ...
- 179k
1
vote
1
answer
110
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 ...
- 74.8k
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 ...
- 167k
4
votes
2
answers
484
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 ...
- 178k
0
votes
1
answer
638
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 (...
- 16.6k
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, ...
- 24.4k
0
votes
0
answers
697
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 ...
- 78.3k
-1
votes
1
answer
53
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 ...
- 18.9k
1
vote
0
answers
141
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
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 ...
CommunityBot
- 1
0
votes
1
answer
48
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?
- 74.8k
1
vote
1
answer
191
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
...
- 42.5k
-1
votes
2
answers
140
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: ...
CommunityBot
- 1
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;
+-----------------------------+------------------+------+-----+---------+-------+
| ...
- 74.8k
1
vote
0
answers
103
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 ...
- 8,370
0
votes
1
answer
113
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
885
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
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 ...
CommunityBot
- 1
3
votes
1
answer
148
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 ...
- 1,011