Tag Info

Hot answers tagged

51

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 ...


13

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 ...


13

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


8

In PostgreSQL, one very good way to deal with EAV structures is the additional module hstore, available for version 8.4 or later. 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 ...


7

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 ...


6

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 ...


6

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) ...


6

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 ...


5

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 ...


5

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 ...


5

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, ...


5

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 ...


5

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 ...


4

A simple CHECK constraint works just fine: $ sqlite SQLite version 3.8.4.1 2014-03-11 15:27:36 ... sqlite> CREATE TABLE table_B( ...> user1, ...> user2, ...> [other stuff], ...> CHECK (user2 < user1) ...> ); sqlite> INSERT INTO table_B VALUES (1, 0); sqlite> INSERT INTO table_B VALUES (2, 3); Error: ...


3

Funny to see how EAV db model is criticized and even considered as an "anti-pattern" by some. As far as I'm concerned, the major downsides are : Learning curve is steeper if you get on a project which already started using EAV a while ago. Indeed, the queries are tough as you greatly increase the number of joins (and tables) and so it will ask more time ...


3

Your example (idea II) looks like it shows how to store the response to the forms. Would you store the form descriptions (created by your admin) in the same fasion, as it: id | name | value ----------------------------------------------------------- 1 | form1|{"question1":"description", "question2":"score"} If you go this route, it means you have to ...


2

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 ...


2

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 ...


2

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 ...


1

I'd suggest you have a think about this system's use cases. Work out in your own mind how the queries will look for the given design and for your alternative. Include scenarios where people move between slots. What do updates look like? Can business constraints be enforced in DRI? Can the desired values still be found? Having actual examples to talk ...


1

The process of pivoting data is often called transposition, as it is similar to the matrix operation of that name in mathematics (http://en.wikipedia.org/wiki/Transpose), though that isn't really what you are doing in your example. In your output you still have the EAV pattern but stored less efficiently. The columns "Entity, A_Name1, A_Value1" is one ...


1

Okay, the amount of data is okay (assuming you don't want to query over 100s of tables!). I think the table structure needs changing a bit to make this easier. tblCsvTable(csvTableID, name, dateCreated) e.g. (1, "my CSV table", '2013-06-24') tblCsvColumn(csvColumnID, columnName, keyCsvTable[FK]) e.g. (5, "name", 1) (6, "email", 1) tblCsvValue(csvTableID, ...


1

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) ...



Only top voted, non community-wiki answers of a minimum length are eligible