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
0
votes
1answer
29 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 ...
0
votes
0answers
37 views

Database table design for product with multiple options and various options offer more options

im having a hard time coming up with a database design for products that have multiple options however in some cases one option may even lead to a new option to appear. Say i have Backpack as a ...
-1
votes
2answers
40 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
68 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
0answers
34 views

Pivot returning null columns in MariaDB / mySQL

I'm trying to pivot a table in a Wordpress Woocommerce database, just for some adhoc reporting, using Case statements. It works well for most columns (the shorter ones), but columns returned with ...
0
votes
1answer
62 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, ...
0
votes
1answer
56 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
0answers
31 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
102 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
53 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
53 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
42 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
39 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
21 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
24 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
133 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
99 views

Query polymorphic relation table

I'm trying to query the following table: +----+------------------+----------------+-------------+-------+ | id | describable_type | describable_id | property_id | value | +----+------------------+----...
1
vote
1answer
241 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
49 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
174 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
69 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
536 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
224 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
64 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
84 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
50 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
190 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
90 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
338 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
83 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
5k 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
174 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
584 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
75 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
350 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
118 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
3k 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
82 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
62 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
118 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
89 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
225 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
708 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
188 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 ...