SELECT * FROM attr_int WHERE entity_id = ? ORDER BY created_at DESC LIMIT 1 SELECT * FROM attr_option WHERE entity_id = ? if( ! multiple) ORDER BY created_at DESC LIMIT 1 SELECT * FROM attr_option, attr_int, attr_relation, attr_text. SELECT * FROM entity_type et LEFT JOIN entity e ON e.entity_type_id = et.id WHERE e.id = ? Users (gender -> options, age -> int, username -> string, etc.) Products (price -> decimal, title -> string, desc -> text, etc.) WHERE g.catalog_id = $nCatalog Designing an EAV database correctly for historical data LEFT JOIN NumberValue as f3 ON f3.good_id = g.id LEFT JOIN NumberValue as f2 ON f2.good_id = g.id LEFT JOIN TextValue as f1 ON f1.good_id = g.id ORDER BY f.sort SELECT g.if, g.name, g.price, WHERE c.id = $nCatalog AND f.table_view = 1 INNER JOIN Field as f ON f.catalog_id = c.id ORDER BY f.sort SELECT f.id, f.name, f.type_of FROM Catalog as c LEFT JOIN CustomValues as val ON tv.good = g.id AND f.id = val.field_id INNER JOIN Field as f ON f.catalog = g.catalog *, F.name, f.type_of, val.text_value, val.number_value, f.sort FROM Good as g WHERE g.id = $nGood AND f.type_of = 'number' LEFT JOIN NumberValues as val ON val.good = g.id AND f.id = val.field_id WHERE g.id = $ nGood AND f.type_of = 'text' LEFT JOIN TextValues as val ON tv.good = g.id AND f.id = val.field_id INNER JOIN Field as f ON f.catalog_id = g.catalog_id *, F.name, f.type_of, val.fValue, f.sort FROM Good as g ) SELECT * FROM Catalog ORDER BY id $stmt = $ pdo-> query ('SELECT * FROM Catalog ORDER BY id') WHERE ea.attribute_code = 'name' CREATE TABLE Catalog ( INNER JOIN catalog_product_entity_varchar cpev ON ea.attribute_id = cpev.attribute_id AND cpe.entity_id = cpev.entity_id INNER JOIN eav_attribute ea ON cpe.entity_type_id = ea.entity_type_id SELECT cpe.entity_id, value AS name FROM catalog_product_entity cpe SELECT * FROM eav_attribute WHERE entity_type_id = 4 AND attribute_code = 'name' SELECT * FROM eav_attribute WHERE entity_type_id = 4 The most common type of "NoSql" database - DynamoDB, Mongo, Cassandra etc - resolve this via taking a different tradeoff.Previous Post Next Post Understanding the EAV data model and when to use it There are exceptions to this of course - if you can know you won't need to make joins between certain parts of your data you can "shard" a database - but in general a SQL database requires "vertical scaling". The database needs to be on one "machine".
![universal database vs eav modeling universal database vs eav modeling](https://media.springernature.com/lw785/springer-static/image/art%3A10.1186%2F1471-2105-5-168/MediaObjects/12859_2004_Article_284_Fig2_HTML.jpg)
Now, in order to make sure all the data that is written or read can be joined for whatever arbitrary query comes in and to make sure all the writes to the database are atomic, consistent, etc there is one restriction.
#UNIVERSAL DATABASE VS EAV MODELING SOFTWARE#
It is flexible in the face of changing software requirements and very storage efficient. This is the data model that powers almost all software on the internet in one way or another. Then when you write data into the database, say by updating someone's age, you either create new rows in the database with INSERT or you find the appropriate rows in the database and change them in place with UPDATE. We call this setup, where data is related by ids and not directly nested, "normalized". JOIN purchase ON purchase.person_id = person.person_id SELECT person.name, person.age, purchase.amount_cents Usually you will also add "indexes" to make common queries faster. purchase_idĪnd then when reading the database, you issue a query that can arbitrarily "join" the data between multiple tables. When you have data that is stored in one table, but related to another, you can "relate" those two pieces of information by foreign keys. I might also not be the right person to give the pitch, but I want to at least try.Īs you are likely aware, a regular SQL database stores data as rows in tables person_id I think an open source version of this in rust would benefit the software community greatly. There are some decent "offshoots", but even those are tied to the Java Virtual machine or the Clojure programming language. It is a proprietary database and all the implementations of the concept behind it are either proprietary or unmaintained. There is a kind of database out there in the world that is really cool called Datomic. I was inspired by this post, which showed that me you can just actually ask for help here.