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
-1
votes
0answers
34 views

Modelling a bitemporal data set in a data warehouse [migrated]

I have a bitemporal EAV type model. There are multiple attribute tables(one for each value data type(int_attributes, float_attributes, etc. )) looking like: |id|field_id|value|as_of_date_start|...
2
votes
1answer
43 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
1answer
46 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 ...
0
votes
1answer
64 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
1answer
23 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
1answer
43 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: ...
1
vote
1answer
231 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
0answers
108 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
1answer
34 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 ...
0
votes
0answers
50 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 ...
0
votes
1answer
38 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
1answer
44 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 ...
0
votes
1answer
31 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
0answers
75 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 ...
0
votes
1answer
61 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 ...
-1
votes
2answers
247 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 ...
3
votes
1answer
90 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 ...
0
votes
1answer
526 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 ...
0
votes
1answer
58 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 ...
0
votes
0answers
27 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 ...
0
votes
1answer
234 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
1answer
59 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
2answers
56 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 ...
0
votes
1answer
43 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, ...
1
vote
1answer
484 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 ...
1
vote
1answer
23 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 | ...
0
votes
1answer
26 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, ...
0
votes
1answer
254 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 ...
0
votes
1answer
239 views

Query polymorphic relation table

I'm trying to query the following table: +----+------------------+----------------+-------------+-------+ | id | describable_type | describable_id | property_id | value | +----+------------------+----...
1
vote
1answer
472 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 ...
0
votes
1answer
89 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
1answer
356 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 ...
2
votes
1answer
731 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 ...
1
vote
1answer
870 views

Opencart mysql query optimize

I am using Opencart 2.x with mysql. I have created a custom module for excel export. My query took more than 2 minutes to execute. So I want to optimize my query. EXPLAIN shows the following image ...
4
votes
3answers
248 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 ...
-1
votes
2answers
72 views

need to change time value stored in VARCHAR column

I am using freeradius base billing system which is using mysql as its backend DB engine. Table structure is as follows, Thousands of users account expiration is stored in VARCHAR column and I cannot ...
-1
votes
1answer
44 views

How to query a table with different values on the same fields?

I'm currently having some trouble with a search query I'm doing in MySQL. I have a table that stores form data for different fields of a course, with two foreign keys (courseid and fieldid). There is ...
1
vote
1answer
139 views

Table with relation to two others (three-way relation?)

I stumbled across a weird situation that one table has relation to two others but record in it can only have a relation to one of the tables. To clarify things, I have tables: attribute, visit, ...
0
votes
1answer
57 views

How best to store complex cost information

I am creating a database to store data related to costs. The cost information can be very varied and I won't be able to fully determine all possible columns currently. An example piece of data might ...
0
votes
1answer
454 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
vote
1answer
138 views

Designing a database structure for huge sets of form data [closed]

I am converting a form that takes up 35 pages of A4 size to a web based version, and will need to retain the data for each form submission in a MySQL database. The form is complex and we could be ...
1
vote
1answer
471 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 ...
1
vote
2answers
89 views

Storing Recurring Datasets

I'm trying to store the following arrays into a database, but due to my inexperience, I don't know how to set it up. It's for a scheduler. A day entry holds three arrays: The 1st array holds the ...
0
votes
2answers
9k views

Join tables with same keys, second table has multiple values for key

I have two tables with shared keys and I'm trying to join them and output the results to one table. The problem I'm having is the second table has multiple values for the shared key. hosts ...
1
vote
3answers
693 views

Best way to store different types of data in a single column, which should be query friendly - MySql

I'm working on a project in which user can create their own web services from UI by simply selecting number of fields (Max 200) and defining datatype for each field. I'm considering following table ...
1
vote
1answer
757 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 ...
1
vote
1answer
81 views

ALTER TABLE online by keeping data as BLOBs

Could you please explain me how extreme de-normalization (store the value as a serialized blob will help to ALTER TABLE online? source Additionally, it's crucial to be able to change the schema: ...
1
vote
2answers
442 views

Replacing too many tables with too many columns?

Background We have a small MySQL database, which logs numeric data from devices. Each device has ~100 variables. List of devices is stored in single table. deviceId, type and version are stored here....
-1
votes
2answers
153 views

When 3rd NF is not the best solution? (Performance)

I have 3 tables: Measurements ('id', 'name') Records ('id', 'measurement_id', 'time', 'number') - Measurement has many records Parameters ('id', 'record_id', 'param', 'value') - Record has many ...
-1
votes
2answers
126 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: ...