Tag Info

It's called Entity-Attribute-Value (also sometimes 'name-value pairs') and it's a classic case of "a round peg in a square hole" when people use the EAV pattern in a relational database. Here's a list of why you shouldn't use EAV: You can't use data types. It doesn't matter if the value is a date, a number or money (decimal). It's always going to be ...

Both excel at a simple task like this. If you end up having big queries where you search for entities that share many attributes ("relational division"), I would expect PostgreSQL at an advantage for its superior index handling. In particular, multiple joins can be combined with bitmap index scans - a feature that is not present in MySQL. It has an ...

Entity Attribute Value (EAV) It is considered to be an anti-pattern by many, including me. Here are your alternatives: use database table inheritance use XML data and SQLXML functions use a nosql database, like HBase

My gut says that any performance gain you get is unlikely to be worth the extra hassle (and potential for bugs ) resulting from needing to enforce the separation and perform multiple lookups in your application logic. If you have a lot of small values and were only querying them and none of the rest you would see some performance gain as more rows would fit ...

This is one (of many) downsides of EAV designs. You can't really improve the JOIN: because of the necessary complexity, a cost based optimiser won't get to the perfect plan. It finds "good enough" Suggestions: don't use a view: use aggregate type queries (eg COUNT(*) = 2 if I match both height and weight) use a trigger to maintain a real (or sparse) ...

You'll have no gains in performance at all. Quick thoughts, not at all an exhaustive analysis: At some point you'll need to UNION these to get a single view and then everything becomes varchar(max) How do you decide length up front? Indexing for searching values? You can't index > 900 bytes Rolling your own "unique" constraints in an EAV would be bad ...

Well...my solution is as follows: I used a dynamic pivot table in a stored procedure. The stored procedure called a View that I created which denormalizes the data. I think used MS Query to hook the stored procedure up to Excel 2010. This loads it up into a nicely formatted table that allows the end user to sort and filter to their heart's content. With ...

EAV is a nightmare for BI tools. I've found a few places that build automated processes that generate a "pivoted" view of the EAV table, as an ETL process daily which drops & recreates the table, with columns for each key. However, depending on how your BI tool works, you will still have to manually add the new attributes that are created by the ...

It sounds like they're trying to optimize the EAV for lookups. However, this clearly sounds like they're not trying to optimize a system for profiled deficiencies, but instead they're trying to optimize via voodoo guesses. Remind them that the first rule of optimization is profiling, so like David Spillett said, until you have a couple hundred million rows ...

In PostgreSQL, one very good way to deal with EAV structures is the additional module hstore. I quote the manual: This module implements the hstore data type for storing sets of key/value pairs within a single PostgreSQL value. This can be useful in various scenarios, such as rows with many attributes that are rarely examined, or semi-structured ...

Supertype/Subtype How about looking into the supertype/subtype pattern? Common columns go in a parent table. Each distinct type has its own table with the ID of the parent as its own PK and it contains unique columns not common to all subtypes. You can include a type column in both parent and children tables to ensure each device can't be more than one ...

Here is what I think is intended in this model: Category is a kind of thing. In a data model it would be an entity type, in a database it would be a table. Attribute is a facet of a thing. In a data model it would be a predicate type, in a database it would be a column. Object is an instance of a thing. In a data model it would be an entity, in a ...

If you have a database that is using the EAV structure, it is possible to query the data a variety of ways. @Simon's answer already shows how to perform a query using multiple joins. Sample Data Used: CREATE TABLE yourtable ([ID] int, [Metric] varchar(6), [Value] int); INSERT INTO yourtable ([ID], [Metric], [Value]) VALUES (1, 'Ht_cm', 190), (1, ...

You don't mention an indexes on the eav table, so I'm assuming you don't have any. It might make sense to add a few partial ones. Depending on the type of queries you're doing, either or both of these might be useful: create index item_attr_weight_item_idx on item_attr(item) where (name = 'weight'); create index item_attr_weight_value_idx on ...

Option 2 is known as "EAV" or Entity-Attribute-Value not relational no DB level constraints requires contortions to read the data unless a simple list But, it depends what you mean by "settings". If you have a few 1000 rows that are not objects and don't require constraints then, yes, use this pattern. This is what SQL Server does with sys.configurations ...

If you don't want to go for a full EAV solution, you could try something like this: product_base ------------ id product_group_id (other fields) product_option_types -------------------- id description product_options --------------- id product_id (fk to product_base.id) product_option_type_id (fk to product_option_types.id) ...

In your case the best approach is a variation on the Entity-Attribute-Value (EAV) model. There are lots of people who shy away from EAV because it is unhelpful in some ways and misused a lot of the time. However, EAV is a solution that works well for your specific requirements. The variation that you want to include for your situation is to abstract the ...