Tag Info

Hot answers tagged

56

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


15

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


14

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


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

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


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

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

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


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


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


5

I can think of three solutions - EAV, XML, and Sparse Columns. The latter is vendor-specific and may not be useful to you. Whichever method you choose, you may wish to consider storing the original request data in a raw format, in a table or flat file. It will make it easy to try new ways of storing the data, allow you to reload data if you discover a ...


5

EAV is not a bad design, per se, it is simply a design that requires a fair amount of forethought and can be wrought with performance issues as the quantity of data rises. It may be that for your system, it would work well. When I designed a system for storing query strings, I had no idea in advance what fields I would be interested in. I created a table ...


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

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

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

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


4

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


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

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


3

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


2

Your choice does not have to either/or - you can have both. At a "physical level" is generally better to use horizontal designs for performance, maintenance and reliability reasons. But the greater rule is: "Don't mix data and metadata". There is nothing intrinsically wrong with building an application that knows how to do CREATE TABLE and ALTER TABLE Foo ...


2

First of all, what you are about to design is probably a VERY bad idea. A much better solution would be to have a dynamic schema where you add new tables and have the application understand how to query those table (you could place them in a schema). This largely avoids all the locking and query plan issues you are bound to run into with this model. There is ...


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

It's not eav - google "celko eav" Not sure about exact syntax, but CHECK (user1 > user2) or similar should work - it does in Firebird embedded. AIUI, SQLite doesn't support subqueries. This isn't one. The constraint will take care of your alice,bob-bob,alice issue. Paul...


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


1

The overall approach is as follows: a) An Entity-Attribute-Value model approach to tackle the attributes of the different devices to a device type. Each device type will have a list of attributes whose values you track b) For each device type, you track the inventory details by serial number which corresponds to a single device. So you would end up ...


1

I would consider trying PostgreSQL's hstore module.



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