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
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 ...
1
vote
1
answer
2k
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
...
- 123
4
votes
3
answers
260
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 ...
- 163
-1
votes
2
answers
85
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 ...
- 111
-1
votes
1
answer
84
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 ...
- 13
1
vote
1
answer
428
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, ...
- 113
0
votes
1
answer
75
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 ...
- 1
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 | ...
- 103
1
vote
1
answer
572
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 ...
- 113
1
vote
1
answer
720
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 ...
- 218
1
vote
2
answers
116
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 ...
- 113
0
votes
2
answers
14k
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
...
2
votes
1
answer
1k
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 ...
- 145
1
vote
1
answer
177
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: ...
- 397
1
vote
2
answers
554
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....
- 113
-1
votes
2
answers
158
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 ...
- 111
-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: ...
1
vote
1
answer
8k
views
Mysql table column to row
i am in difficulties in doing following table (mysql). i have stdent-id,questions,answers and questions-id of a table. below given,
is there any way to output table something below output table?
My ...
6
votes
1
answer
140
views
Can I do a conditional JOIN or conditional SELECT based on the data in a table?
I think I have a problem where EAV is the best choice.
I am planning my tables as follows.
motherboard
motherboard_id | model_name
int | varchar
Example:
motherboard_id | model_name
---...
- 163
0
votes
1
answer
253
views
MySQL - Store date as a value with an ID?
I've got a database with the following structure:
Elements table
+------------+---------------+---------------+-----+
| element_id | user_id | element_data2 | ... |
+------------+---------------...
- 103
1
vote
1
answer
121
views
Are lots of columns or joins better in Django
I am writing a simple Django app that stores and displays user objects with lots of attributes.
The user may have up to three hundred attributes.
These attributes however, can be grouped off.
For ...
- 11
3
votes
0
answers
94
views
Optimality of Columns vs. Complementary Table in MySQL
I'm starting work on a project where I'll have a table of entries as well as a table of descriptors.
The entries will be updated infrequently, if ever, and the descriptors table will be largely ...
- 173
2
votes
0
answers
126
views
EAV with very few of each entity
I have data that fits an entity-attribute-value structure but with very few entries (same attributes but different values) per entity.
The main characterizations of my data are as follows:
~700 ...
- 185
1
vote
1
answer
284
views
Disadvantage of Changing database schema - Insert or delete new columns/tables in middle
As a read in lot of links , for e-commerce shopping sites, as it may require to add lot of attributes in regular basis, is it okay if we go with Flat tables ?
Whats the disadvantage of changing ...
- 211
2
votes
1
answer
445
views
best way to add Entity id & entity types in database
we are doing shopping site with mysql with eav model to store values.
in below image , 2 types of entity types are there under products_information table : 1) Mobile 2) T-shirts
I wanted to know in ...
- 211
1
vote
1
answer
2k
views
How should I handle Products with different attributes in my database structure?
We have an e-commerce site (web application) built with PHP.
There are different types of products like mobiles, tshirts, watches, books.
A mobile can have attributes like, for example, ram, model ...
- 211
1
vote
1
answer
228
views
Best way to create dynamic table for search
There is a table of documents:
CREATE TABLE `docs` (
`doc_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`doc_uuid` binary(16) NOT NULL,
`sender_uuid` varchar(40) NOT NULL,
`receiver_uuid` ...
- 133
5
votes
1
answer
3k
views
Many to many between lots of tables?
TL;DR: How could I set up many-to-many relationship between that many tables and is it viable/recommended?
Each body type has its own table
Each car in each body type table can has several features
...
- 153
0
votes
1
answer
320
views
Designing table for document annotation with future columns
I am working on a web application that allows students/researchers to annotate PDF documents shallowly by (manually) counting interesting phenomena. Right now, there is a total of about 45 of these ...
- 53
0
votes
1
answer
59
views
DB Design - 1 column for each dataset OR dataset_name AND dataset_value?
I am creating a database table for storing numeric time-series data sets regarding cities that will be accessed via a web app. Primarily annual, quarterly and monthly data will be stored in it. ...
- 143
1
vote
0
answers
127
views
how to edit kvp data as crosstab/pivot
I am keeping data in key/value pairs. I know how to convert that data into crosstab form using queries like this:
<!-- language: lang-sql -->
SELECT master.tag,
MAX(CASE WHEN data.key = ...
- 11
1
vote
5
answers
1k
views
Should I have a column for every data type or 1 column that stores everything
I have a database table that currently looks like this:
PageField
-Id (Int, PK)
-FieldType (String) Can be Text, Decimal, Integer or Bit
-Value (This stores the value regardless of the FieldType, so ...
- 225
3
votes
2
answers
98
views
Using table values as column
I have 3 tables:
Products, containing - id, name and description
Attributes, containing - id, name
prod_attri_rel, containing - id, prod_id, attri_id and value
Products looks like:
1 | test | just ...
- 151
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 ...
- 311
1
vote
0
answers
763
views
Alternative to EAV to store data with an unknown structure
For my bachelor thesis I have to build a reseach database which has to meet the requirement of provenance. In the present case it means that among other things I have to store the results of my ...
- 111
30
votes
5
answers
147k
views
How to optimize very slow SELECT with LEFT JOINs over big tables
I was googling, self-educating & looking for solution for hours but with no luck. I found a few similar questions here but not this case.
My tables:
persons (~10M rows)
attributes (location, age,...
- 411
4
votes
3
answers
224
views
Some users can have a fixed amount of additional fields, EAV or not?
Say I have to store data about two different types of person roles or occupations, i.e., Consumers and Merchants, and both types of roles share a single base table called User.
In this respect, ...
- 205
0
votes
1
answer
125
views
Flexibility vs Performance question
I'm in the middle of designing a database, and have a question for the veterans in regards to scalability.
Currently, I have table A, which represents some object, and table B, which contains key-...
- 25
3
votes
1
answer
318
views
What's the best way to store and search through many product specifications
Let's say I have a lot of products and all of those products have a lot of specifications. These specifications can change at any time (e.g. "colour" could suddenly be available for new products) and ...
- 31
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 ...
- 23
5
votes
1
answer
2k
views
A good use case for Postgres JSONB?
I am looking to create an Asset Management System to track assets (laptops/desktops, monitors, keyboards, mice, bags, software keys, etc), basically anything that can be assigned to an employee. My ...
- 151
2
votes
0
answers
416
views
Migrating schema from Entity-Attribute-Value (EAV) model to proper tables
I have an Entity-Attribute-Value (EAV) database in Oracle 11. For many, many reasons, I would like to model this using actual tables. Changing all the client code at once is not possible.
Existing ...
- 131
15
votes
4
answers
11k
views
Proper way to store a value that could be multiple different types
I have an Answers table and a Questions table.
The Answers table has a value, but depending on the question, this value could be a bit, nvarchar, or number (so far). The Question has a notion of what ...
- 291
0
votes
2
answers
88
views
Multiple types of parts with multiple types of information (unspecified amount) [duplicate]
I'm having trouble designing a database for a project I was working on recently. It's to do with a garage management system.
So I've realised I need to store multiple types of parts (Tyres, engines, ...
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 ...
- 203
6
votes
1
answer
1k
views
Design for scientific data. Data table with hundreds of columns or data table with a generic value column and hundreds of rows (EAV)?
I am trying to improve the data storage in biomedical science lab where I recently started working. The existing workflow is atrocious, involving many differently formatted Excel sheets that all get ...
- 63
2
votes
2
answers
9k
views
How to convert rows into columns and query on them in Mysql?
I have three tables Patients, which contains the name of my patients, Controls which represents the UI controls which could be drawn for each illness , and ControlsValues table which contains the ...
- 121
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
...
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 ...
- 141
1
vote
1
answer
2k
views
Use a set database schema or EAV design? [duplicate]
I have already created a database design with a set schema.
The database schema is for a web quotation form. PS it is not a simple quotation form and hence need to customise the form, form builders ...
- 11