Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

0
votes
1answer
49 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
25 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
0answers
23 views

Joining multiple (10+) tables IN CASE attribute exists, min 1, max all

I've asked a version of the question here Allow Null Columns vs separate 1-to-many tables and here Multiple JOINs of possible properties in a 'flower' arrangement - acceptable design? but ...
0
votes
1answer
59 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
52 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
52 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
41 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
30 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
20 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
23 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
118 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
63 views

Query polymorphic relation table

I'm trying to query the following table: +----+------------------+----------------+-------------+-------+ | id | describable_type | describable_id | property_id | value | +----+------------------+----...
1
vote
1answer
180 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
47 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
125 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 ...
1
vote
0answers
50 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
444 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
219 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
62 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
38 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
71 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
47 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
121 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
85 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
298 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
78 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
3k 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
148 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
510 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
70 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
314 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
152 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 ...
0
votes
2answers
111 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
1answer
2k 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
1answer
112 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 ---...
0
votes
1answer
80 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 | ... | +------------+---------------...
1
vote
1answer
72 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 ...
2
votes
0answers
60 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 ...
2
votes
0answers
115 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 ...
1
vote
1answer
88 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 ...
2
votes
1answer
199 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 ...
1
vote
1answer
657 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 ...
1
vote
1answer
158 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` ...
2
votes
1answer
1k 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 ...
0
votes
1answer
168 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 ...
0
votes
1answer
49 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. ...
1
vote
0answers
99 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 = ...
1
vote
5answers
448 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 ...
3
votes
2answers
88 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 ...
6
votes
3answers
24k 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 ...