EAV attribtutes
Moderator: General Moderators
Re: EAV attribtutes
This could replace a full blown EAV system, your data structure could still be represented as metadata. Thats the beaty of it, if its written portably it doesn't matter if its being used in a custom fields pattern, or for powerofq's new framework LOL, either way it would allow the programmer to access the data in faster more efficient ways
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: EAV attribtutes
EAV does not eliminate SQL from the system. What it does is eliminiate the need to know the table and column names for the query -- because they are always the same. But you are still making the same number and probably more queries, hence there is plenty of SQL.allspiritseve wrote:I guess I'm confused... wouldn't an EAV take the place of create/alter SQL? Are you trying to use some interface to an EAV system to actually create/alter tables?
That's why I was thinking that we could create a classes that would generate all of the SQL queries to read/write the records. It would have an OO finder interface that you could give logic conditions to, and it would generate the SQL to do that query.
Then another set of classes that would know the metadata and could extract or build data to me saved. It could be as simple as knowing the metadata tag names and serializing/unserializing an assoc array. The data would come back as a standard record, except the column name would be variable.
We could always build a more full blown or integrated system over the top of those two parts later if needed. Or you could develop a wrapper to plug-in the parts if, for example, we found there were multiple metadata styles.
PS jspro2 - what is that code you posted for?
(#10850)
Re: EAV attribtutes
thats from virtuemart, a joomla plugin, i had to play around with it to quote out a job for a client and I noticed it was dynamically creating tables in my database like ( the "diagonal" size thing was a parameter I put in for a product variation as user input )
CREATE TABLE `jos_vm_product_type_1` (
`product_id` int(11) NOT NULL,
`diagonal_size` int(11) default NULL,
PRIMARY KEY (`product_id`),
KEY `idx_product_type_1_diagonal_size` (`diagonal_size`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- Dumping data for table `jos_vm_product_type_1`
--
INSERT INTO `jos_vm_product_type_1` (`product_id`, `diagonal_size`) VALUES
(7, 17);
CREATE TABLE `jos_vm_product_type_1` (
`product_id` int(11) NOT NULL,
`diagonal_size` int(11) default NULL,
PRIMARY KEY (`product_id`),
KEY `idx_product_type_1_diagonal_size` (`diagonal_size`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- Dumping data for table `jos_vm_product_type_1`
--
INSERT INTO `jos_vm_product_type_1` (`product_id`, `diagonal_size`) VALUES
(7, 17);
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: EAV attribtutes
That's not what an EAV system would do though. In EAV, "diagonal_size" would be an attribute. You could store one or more of them per product_id (which would be the entity in EAV parlance). You could then give a "diagonal_size" value (like '27 inch' for TVs) to any product that needed it.
An interesting part of building a system is that supported EAV is that the entity and the attribute can be stored in the EAV table, or one or both can be foreign keys into other tables. In your example above they might both be foreign keys because the product_id would be a relation to the product table and the attribute would probably be to a table of all the different "allowed" attributes for products. Or it might be a mix, like a user defined tag system where the entity would be a relation to the user table, but the tags could be stored directly in the EAV table.
We could allow the entity and/or attribute to be a foreign key or actual data.
An interesting part of building a system is that supported EAV is that the entity and the attribute can be stored in the EAV table, or one or both can be foreign keys into other tables. In your example above they might both be foreign keys because the product_id would be a relation to the product table and the attribute would probably be to a table of all the different "allowed" attributes for products. Or it might be a mix, like a user defined tag system where the entity would be a relation to the user table, but the tags could be stored directly in the EAV table.
We could allow the entity and/or attribute to be a foreign key or actual data.
(#10850)
- allspiritseve
- DevNet Resident
- Posts: 1174
- Joined: Thu Mar 06, 2008 8:23 am
- Location: Ann Arbor, MI (USA)
Re: EAV attribtutes
Right... the tables are always the same, so you wouldn't need "CREATE TABLE"/"ALTER TABLE" queries (which are for creating and altering tables, yes?) Instead, you'd have INSERT and UPDATE, which take the place of CREATE/ALTER in the EAV.arborint wrote:EAV does not eliminate SQL from the system. What it does is eliminiate the need to know the table and column names for the query -- because they are always the same. But you are still making the same number and probably more queries, hence there is plenty of SQL.allspiritseve wrote:I guess I'm confused... wouldn't an EAV take the place of create/alter SQL? Are you trying to use some interface to an EAV system to actually create/alter tables?
Re: EAV attribtutes
hmm, gotcha, itd be a custom fields library, and EAV would be one of the adapters. It wouldn't replace or reinvent EAV it would let the programmer decide how to store the data best. For instance for something like product attributes where the frontend is likely to reqeust a lot of joins, EAV would not be an ideal solution, but a lot of software still uses it, with the library they could use EAV today, and a regular relational model tomorrow.
What the virtuemart code was doing was what you described arboint, it just allows groupings of attributes into "product types", then you can assign product types to products, so the user can tell the program a given product has all the attributes that a "monitor type" entity would have.
Additionally the library could allow these entity types, multiple types per entity, and mixing and matching of custom fields that don't belong to a type as well. Custom fields that belong to a "type" should be in a common table for faster data access, if the fields need to be broken up later the code should handle that as well ( to avoid a sparse matrix if domain requirements change ).
Hmm, looking at a schema for an EAV system and I guess what I am proposing is a better way for it to handle "attribute sets"
What the virtuemart code was doing was what you described arboint, it just allows groupings of attributes into "product types", then you can assign product types to products, so the user can tell the program a given product has all the attributes that a "monitor type" entity would have.
Additionally the library could allow these entity types, multiple types per entity, and mixing and matching of custom fields that don't belong to a type as well. Custom fields that belong to a "type" should be in a common table for faster data access, if the fields need to be broken up later the code should handle that as well ( to avoid a sparse matrix if domain requirements change ).
Hmm, looking at a schema for an EAV system and I guess what I am proposing is a better way for it to handle "attribute sets"
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: EAV attribtutes
Yes and no. You still want the CREATE to create the EAV table. And if you look at what I discussed above you might want to customize the CREATE depending on whether the entitiy and attribute were foreign keys or actual values (in which case you would need to specify the column types).allspiritseve wrote:Right... the tables are always the same, so you wouldn't need "CREATE TABLE"/"ALTER TABLE" queries (which are for creating and altering tables, yes?) Instead, you'd have INSERT and UPDATE, which take the place of CREATE/ALTER in the EAV.
The other thing is that this is a "system" because the EAV often relates to some other tables of entities -- so when you create it you are creating attribute/value pairs for something, like users or products. So there might need to be some containing class that understood the tables involved.
And do we then need a 'eav' table to contain metadata about any eavs in use. Or do we have existential records in the EAV that contain information about the EAV itself? These are all implementation specifics that we might want to provide support for. Perhaps we could support both and either system would provide a standard Value Object to the EAV to initialize it. Or you could initialize it manually.
(#10850)
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: EAV attribtutes
The sense I get from EAV is that the attribute value is used as a virtual column name. That is the essence of the design -- that any ID can have one or more column/value pairs associated with it.jshpro2 wrote:Additionally the library could allow these entity types, multiple types per entity, and mixing and matching of custom fields that don't belong to a type as well. Custom fields that belong to a "type" should be in a common table for faster data access, if the fields need to be broken up later the code should handle that as well ( to avoid a sparse matrix if domain requirements change ).
Hmm, looking at a schema for an EAV system and I guess what I am proposing is a better way for it to handle "attribute sets"
(#10850)
Re: EAV attribtutes
Right, but if multiple virtual column names make up an "entity" type,
for instance lets go back to the products example.. if a product is of type "monitor", then that monitor product has the properties that a monitor would, it may have diagonal size, brightness, contrast ratio etc.. also since its a product it would of course have a price, depth, height, etc.. In other words the program provides an abstract product entity and the user should be able to arbitrarily define new "objects" that extend that. ( and also why not objects that extend their extended objects to the Nth level )
In essence the user should be able to do primitive domain modeling through a GUI, so they can change their platform to meet changing business needs
( each time a new data model is extended a table is created, so an object never deals with fields it isn't concerned with, and less row lookups at run-time. Also more elegant from a DBA standpoint )
It would differ from EAV because EAV is just a list of fields, sure you could implement EAV namespaceing but you're still creating a database within the database.. which has its places ( if the fields are so abstract in themselves that they dont belong to entity "types" ), but let's say you need to implement a power search like newegg has:
http://www.newegg.com/Store/SubCategory ... ategory=68
in this case EAV would not be ideal ( mainly for performance reasons )
Also any new "virtual models" the user defines should in theory directly correspond to an object at runtime.. why not? and why should the programmer have to worry about all the different models that would need to be created, he should only have to define each type of model relation once, and then let the user design the problem scenario ( a fish store might have fish tanks, that need fish inventory per tank, etc.. while a grocery store has an entirely different problem area )
maybe while theyre designing their relational database structure we have it detect a bad design and provide advice
while Microsoft chose to use a paperclip to suggest document formatting we can have powerofQs face popup on the screen
for instance lets go back to the products example.. if a product is of type "monitor", then that monitor product has the properties that a monitor would, it may have diagonal size, brightness, contrast ratio etc.. also since its a product it would of course have a price, depth, height, etc.. In other words the program provides an abstract product entity and the user should be able to arbitrarily define new "objects" that extend that. ( and also why not objects that extend their extended objects to the Nth level )
In essence the user should be able to do primitive domain modeling through a GUI, so they can change their platform to meet changing business needs
( each time a new data model is extended a table is created, so an object never deals with fields it isn't concerned with, and less row lookups at run-time. Also more elegant from a DBA standpoint )
It would differ from EAV because EAV is just a list of fields, sure you could implement EAV namespaceing but you're still creating a database within the database.. which has its places ( if the fields are so abstract in themselves that they dont belong to entity "types" ), but let's say you need to implement a power search like newegg has:
http://www.newegg.com/Store/SubCategory ... ategory=68
in this case EAV would not be ideal ( mainly for performance reasons )
Also any new "virtual models" the user defines should in theory directly correspond to an object at runtime.. why not? and why should the programmer have to worry about all the different models that would need to be created, he should only have to define each type of model relation once, and then let the user design the problem scenario ( a fish store might have fish tanks, that need fish inventory per tank, etc.. while a grocery store has an entirely different problem area )
maybe while theyre designing their relational database structure we have it detect a bad design and provide advice
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: EAV attribtutes
Well I am confused. I think that there might be two EAV tables in your NewEgg example above -- one for Product SKUs and one for Product Types. So for the Power Search example (that you not link directly to it
), the EAV would look like:
So the entity becomes a Product Type and the attribute is a search category related to the Product Type. The actual values are the search terms. You could build your Power Search from that.
If you did your product table as an EAV then it would be something like:
But you could also have EAVs to extend existing product or category tables to allow virtual columns in addition to the fixed columns in 'regular' tables.
Code: Select all
'Flash Memory' 'Product Type' OEM
'Flash Memory' 'Product Type' Recertified
'Flash Memory' 'Product Type' Retail
'Flash Memory' 'Manufacturer' Kodak
'Flash Memory' 'Manufacturer' Sony
'Flash Memory' 'Manufacturer' Hewlett-Packard
'Flash Memory' 'Capacity' 4GB
'Flash Memory' 'Capacity' 8GB
'Flash Memory' 'Capacity' 16GBIf you did your product table as an EAV then it would be something like:
Code: Select all
'N82E16820220254' 'Category' 'Flash Memory'
'N82E16820220254' 'Price' '43.99'
'N82E16820220254' 'Rebate' '10.00'
'N82E16820220254' 'RebateCode' 'abc1234'
'N82E16820220254' 'Image' '20-220-254-01.jpg'(#10850)
Re: EAV attribtutes
That's not normalized though
think about the difference in SQL queries
think about the extra work the database has to do if you were storing foreign keys, doing complex querying, etc...
if there is a "flash memory" table, instead of a group of virtual fields, than less rows have to be looked at, less data has to be joined, more efficient indexing can be built, which for some data sets may mean new ways of querying the data is possible
Just an idea... I'll probably write something maybe I'll post it, I'm busy with other stuff currently though... just bouncing my ideas around to make sure they arent *too* off the wall..
( and I meant the advanced search, not power search, which shows # of rows for each entity / attribute combination, but same diff )
Also we're assuming the programmer would only put the entity and attribute columns in his where clause, there might be a website_id and store_id column, there might be other factors we aren't thinking about that would make the EAV solution even less appealing
think about the difference in SQL queries
think about the extra work the database has to do if you were storing foreign keys, doing complex querying, etc...
if there is a "flash memory" table, instead of a group of virtual fields, than less rows have to be looked at, less data has to be joined, more efficient indexing can be built, which for some data sets may mean new ways of querying the data is possible
Just an idea... I'll probably write something maybe I'll post it, I'm busy with other stuff currently though... just bouncing my ideas around to make sure they arent *too* off the wall..
( and I meant the advanced search, not power search, which shows # of rows for each entity / attribute combination, but same diff )
that was part of my proposal, it should recognize when fields would be better stored as a regular table and visa versa, as the code requests to add and delete fields the system may create and delete database structures behind the scene, optimized to a given scenario ( which the programmer could explicitly override, or provide "hints" on how to best index his structure )arborint wrote:But you could also have EAVs to extend existing product or category tables to allow virtual columns in addition to the fixed columns in 'regular' tables.
Also we're assuming the programmer would only put the entity and attribute columns in his where clause, there might be a website_id and store_id column, there might be other factors we aren't thinking about that would make the EAV solution even less appealing
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: EAV attribtutes
There is no question that an EAV is slower than an optimized solution in normal situations. The point of an EAV is to provide flexibility when the potential column count would be huge using normal techniques. I am not sure what your proposals is. I was just discussing EAV as a tool that could be used in a range of possible solution -- from the entire table to just a specific subset of the data. I wanted to show some actual data to show the specifics of EAV rather than just discussing it in the abstract.
EAV would not have universal use for most of us, but could be used to good effect in specific situations (like you product types example). Or you could use it like powerofq has where it becomes the entire system. He seems to like standardizing/simplifying the development side and does not mind the performance hit in his applications. All the above are possible uses.
EAV would not have universal use for most of us, but could be used to good effect in specific situations (like you product types example). Or you could use it like powerofq has where it becomes the entire system. He seems to like standardizing/simplifying the development side and does not mind the performance hit in his applications. All the above are possible uses.
(#10850)
Re: EAV attribtutes
Well is there an opensource "driver" for working with EAV in PHP?
If so I was proposing extending it to also create dynamic table structures, in addition to EAV.
If not I was proposing building something that did both, in such a way the programmer could switch between storage mechanisms in a programmatic manner (with a flag)
If so I was proposing extending it to also create dynamic table structures, in addition to EAV.
If not I was proposing building something that did both, in such a way the programmer could switch between storage mechanisms in a programmatic manner (with a flag)
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: EAV attribtutes
I don't think EAV works at the driver level. It's Triplestore that is implement at the database level. EAV is just a different table arrangement and SQL to solve the same problem.
(#10850)
Re: EAV attribtutes
I meant driver as in adapter. It would be a set of classes. lol man you're so technical, ill post some code if I build it