Stack Exchange Network

Stack Exchange network consists of 174 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
Make your voice heard. Take the 2019 Developer Survey now

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
42 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
49 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
22 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
24 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
18 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 | ...
-1
votes
0answers
47 views

Critique on my attempt at Normalizing MySQL database with Foreign Keys (Join on 8 Tables?)

edit: Here is my new derivative with the products id instead of MPN being the foreign key. Table #1: Products ╔════╦═════╦═══════╦════════════╦════════╦═════════════╦══════════════╗ ║ id ║ MPN ║ ...
-1
votes
0answers
44 views

Create dynamic mysql queries in php safely

I am building a system which is dynamically expandable, new tables are dynamically created in the system based on end user actions, due to this there is no way for me to be able to 'code in' all ...
1
vote
0answers
55 views

MySQL EAV structure and query optimization [closed]

I have designed a EAV table structure as follows customers TABLE _______________ id name 1 Ben 2 Joe meta_fields TABLE __________________________ id name type 1 ...
0
votes
0answers
45 views

Is it possible and a good way to construct database in a dynamic way?

My question is about the specific scenario and what approach I should take in order to make loose coupling and open/closed principle the best way. I'll explain my scenario in a deep detail so that you ...
0
votes
1answer
22 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
99 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
40 views

Query polymorphic relation table

I'm trying to query the following table: +----+------------------+----------------+-------------+-------+ | id | describable_type | describable_id | property_id | value | +----+------------------+----...
1
vote
1answer
92 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
40 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
75 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
37 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
325 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
210 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
58 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
35 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
55 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
44 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
60 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
73 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
247 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
73 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
2k 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 ...
0
votes
0answers
46 views

What is the best way to save extra metadata / fields or sub-types

I have a table in my database that represents an object with simple fields, say ID, Title and DateAdded. I can have other "sub-types" of this object, which have additional specific fields. I first did ...
1
vote
3answers
128 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 ...
0
votes
1answer
427 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
62 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: ...
0
votes
2answers
281 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
141 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
103 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
110 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
0answers
144 views

Want to join table secondary table value as column

One table customer_grid_flat have column entity_id, name, email second table customer_entity_int have column as below value_id,entity_id,attribute_id,value Third table eav_attribute have column as ...
0
votes
1answer
77 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
71 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
56 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
106 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
82 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
157 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
593 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
116 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
162 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
47 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
94 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
381 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 ...