The entity attribue values or EAV is a design that tries to accommodate with a lack of specifications. In our application, we have to deal with parameters and new parameters may be added at each release. It’s not clear which parameters we need, we just want a place to manage them easily, and we are already using a database server after all. So there we go:
.. The model makes it very easy to add things to it, and very difficult to make sense of the accumulated data, or to use them effectively in SQL, making it an anti-pattern.
Multiple Values per Column
create table tweet
(
id bigint primary key,
date timestamptz,
message text,
tags text
);
Data would then be added with a semicolon separator, for instance, or maybe a pipe | char, or in some cases with a fancy Unicode separator char such as §, ¶ or ¦. Here we find a classic semicolon:
id │ date │ message │ tags
════════════════════╪══════╪═════════╪════════════════════════
720553530088669185 │ … │ … │ #NY17
720553531665682434 │ … │ … │ #Endomondo;#endorphins
(2 rows)Several things are very hard to do when you have several tags hidden in a text column using a separator:
Tag Search
To implement searching for a list of messages containing a single given tag, this model forces a substring search which is much less efficient than direct search.