Database Administrators Stack Exchange
Database Administrators 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.

113 questions
71
votes
5 answers

Is there a name for this database schema of key values?

We process a routine data feed from a client who just refactored their database from a form that seems familiar (one row per entity, one column per attribute) to one that seems unfamiliar to me (one row per entity per attribute): Before: one column…
prototype
  • 823
  • 1
  • 7
  • 6
26
votes
5 answers

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,...) links (M:M) between persons and attributes (~40M…
Martin
  • 361
  • 1
  • 4
  • 11
18
votes
3 answers

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 this seems the most likely solution for me and I'm…
lesandru
  • 311
  • 1
  • 2
  • 6
13
votes
2 answers

Alternative to EAV for dynamic fields in a star schema data warehouse

I need to support dynamic fields and values in a big datawarehouse for storing API requests log, my user case is that I need to store all API requests query string and able to perform query against them in the future (so it is not just storage, so I…
Howard
  • 249
  • 1
  • 5
  • 10
13
votes
4 answers

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 its intended answer value type should be. It will…
David Garrison
  • 271
  • 2
  • 9
11
votes
3 answers

Inventory database structure when inventory items have varying attributes

I'm building an inventory database to store enterprise hardware information. The devices the database keeps track of range from workstations, laptops, switches, routers, mobile phones, etc. I'm using device serial numbers as the primary key. The…
TheSecretSquad
  • 571
  • 2
  • 6
  • 14
7
votes
2 answers

Efficient query for an advanced EAV model

I'm kind of stuck on how to build an effecient query which returns the data of the following EAV structure. Today, a product table already exists, containing 4 fixed fields. We want to upgrade the system, allowing an infinite amount of additional…
Zeep
  • 95
  • 1
  • 4
6
votes
1 answer

Why is an additional filtered statistic being ignored (EAV schema)?

I'm trying to improve a row estimate for this sub-query (of a larger query). The estimate is showing 1266 rows. The actual is 117k rows. This particular property (EAV schema) only has two values defined for it (2 and 3): declare @pPropVal…
crokusek
  • 1,956
  • 3
  • 21
  • 32
6
votes
1 answer

EAV structure explained in Layman's terms

I've read several articles online that explain how EAV structures work, but there are different interpretations of them. Some articles mention that it's a representation of a data model metadata (or schema). Other articles describe an EAV…
MacGyver
  • 1,995
  • 14
  • 41
  • 57
6
votes
2 answers

Which is better for long term database scalability: adding columns or having key/value store

For application, which has few (5-10) settings at the moment but will have a lot more (up to 100) in the future which would be better approach: to add new columns to settings table every time new setting is introduced to have settings table as…
valentinas
  • 163
  • 1
  • 4
6
votes
1 answer

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 ---------------------------- 1 | Asus…
Lord Loh.
  • 163
  • 5
6
votes
1 answer

PostgreSQL versus MySQL for EAV structures storage

Is there any practical difference or what are the advantages of using Postgres 9.x versus MySQL 5.x for storing EAV (Entity-Attribute-Value) structures, especially in terms of read performance?
Alex
  • 163
  • 4
6
votes
1 answer

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 way to store these kinds of things. In the EAV…
coussej
  • 203
  • 3
  • 7
5
votes
1 answer

Help with "Flexible" vs Fixed Tables

We are designing a database in SQL Server to handle sales commissions. A diagram of the current schema looks like this: There are only a couple of us on the project, and our boss dropped this design on us. I'm a novice, but I was concerned about…
Zairja
  • 215
  • 1
  • 2
  • 6
5
votes
3 answers

Optimizing query using view on EAV structure

An application is writing into a database that follows an EAV structure, similar to this: CREATE TABLE item ( id INTEGER PRIMARY KEY, description TEXT ); CREATE TABLE item_attr ( item INTEGER REFERENCES item(id), name TEXT, …
Bruno
  • 1,447
  • 3
  • 16
  • 28
1
2 3 4 5 6 7 8