The Entity
The entity is a foreign key into an “Objects” table that records common information about every “object” (thing) in the database – at the minimum, a preferred name and brief description, as well as the category/class of entity to which it belongs. Every record (object) in this table is assigned a machine-generated Object ID.
The major benefit to a central objects table is that, by having a supporting table of object synonyms and keywords, one can provide a standard Google-like search mechanism across the entire system where the user can find information about any object of interest without having to first specify the category that it belongs to. (This is important in bioscience systems where a keyword like “acetylcholine” could refer either to the molecule itself, which is a neurotransmitter, or the biological receptor to which it binds.)
The Value
Coercing all values into strings, as in the EAV data example above, results in a simple, but non-scalable, structure: constant data type inter-conversions are required if one wants to do anything with the values, and an index on the value column of an EAV table is essentially useless. Also, it is not convenient to store large binary data, such as images, in Base64 encoded form in the same table as small integers or strings. Therefore larger systems use separate EAV tables for each data type (including binary large objects), with the metadata for a given attribute identifying the EAV table in which its data will be stored. This approach is actually quite efficient because the modest amount of attribute metadata for a given class or form that a user chooses to work with can be cached readily in memory. However, it requires moving of data from one table to another if an attribute’s data type is changed. (This does not happen often, but mistakes can be made in metadata definition just as in database schema design.)
The Attribute
In the EAV table itself, this is just an Attribute ID, a foreign key into an Attribute Definitions table, as stated above. However, there are usually multiple metadata tables that contain attribute-related information, and these are discussed shortly.
Entity-Attribute-Value model (EAV), also known as object-attribute-value model that is used in circumstances where the number of attributes (properties, parameters) that can be used to describe a thing (an “entity” or “object”) is potentially very vast
Let’s think of a department store database. These databases are responsible for managing endless amounts of products and product brands.
It is innately obvious that the product names wouldn’t be hard-coded as the names of the columns in a table. Alternatively one department’s product descriptions in a product table may function as follows: purchases/sales of an individual item are recorded in another table that would have separate rows with a way to use the product ID for referencing.
An Entity-Attribute-Value design normally involves a solitary table with three columns, these columns most often contain data referring to; the entity, an attribute, and a value for that attribute.
In this design one row actually stores a single fact, in a traditional table that has one column per attribute, one row stores a set of facts. The Entity-Attribute-Value design is applicable when the number of parameters that could apply to an entity is significantly more then those that truly apply to a single entity.
By creating a table or even a set of tables with thousands of columns would not be the best choice of action, the vast majority of the columns would be unacceptable, also the user interface would be obsolete with out an extremely elaborate logic that could hide groups of columns based on the data that has been entered in the previous columns.
In a good E-commerce database, you will store classes of options (like TV resolution then have a resolution for each TV, but the next product may not be a TV and not have “TV resolution”). How do you store them, search efficiently, and allow your users to setup product types with variable fields describing their products? If the search engine finds that customers typically search for TVs based on console depth, you could add console depth to your fields, then add a single depth for each tv product type at run time.
Benefits
* Flexibility. There are no arbitrary limits on the number of attributes per entity. The number of parameters can grow as the database evolves, without schema redesign.
* Space-efficient storage for highly sparse data: One need not reserve space for attributes whose values are null.
* A simple physical data format with partially self-describing data. Maps naturally to interchange formats like XML (the attribute name is replaced with start-attribute and end-attribute tags.)
* For databases holding data describing rapidly evolving scientific domains, insulation against consequences of change and potential domain independence .
Handling EAV-data
Identify your entities and add those entities in entity_type table, here we have specify 2 entities
1st is user and second is address
CREATE TABLE IF NOT EXISTS `entity_type` (
`entity_type_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`entity_type_id`)
) ENGINE=MyISAM;
INSERT INTO `entity_type` (`entity_type_id`, `name`) VALUES
(1, ‘user’),
(2, ‘address’);
_______________________________________
then identify your attributes and add those all attributes in this table, you can create a dynamic html form by using this table
CREATE TABLE IF NOT EXISTS `attribute` (
`attribute_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`attribute_code` varchar(255) NOT NULL DEFAULT ”,
`backend_type` enum(‘static’,'datetime’,'decimal’,'int’,'text’,'varchar’) NOT NULL DEFAULT ‘static’,
`frontend_input` varchar(50) DEFAULT NULL,
`frontend_label` varchar(255) DEFAULT NULL,
`is_required` tinyint(1) unsigned NOT NULL DEFAULT ’0′,
`is_user_defined` tinyint(1) unsigned NOT NULL DEFAULT ’0′,
`default_value` text,
`is_searchable` tinyint(1) unsigned NOT NULL DEFAULT ’0′,
PRIMARY KEY (`attribute_id`)
) ENGINE=InnoDB;
INSERT INTO `attribute` (`attribute_id`, `attribute_code`, `backend_type`, `frontend_input`, `frontend_label`, `is_required`, `is_user_defined`, `default_value`, `is_searchable`) VALUES
(1, ‘firstname’, ‘varchar’, ‘text’, ‘First Name’, 1, 0, ”, 0),
(2, ‘lastname’, ‘varchar’, ‘text’, ‘Last Name’, 1, 0, ”, 0),
(12, ‘region’, ‘varchar’, ‘text’, ‘State/Province’, 1, 0, ”, 0),
(14, ‘postcode’, ‘varchar’, ‘text’, ‘Zip/Postal Code’, 1, 0, ”, 0),
(15, ‘city’, ‘varchar’, ‘text’, ‘City’, 1, 0, ”, 0),
(17, ‘telephone’, ‘varchar’, ‘text’, ‘Telephone’, 1, 0, ”, 0),
(97, ‘description’, ‘text’, ‘textarea’, ‘Description’, 1, 0, ”, 1);
_______________________________________
You need to Identify a entity_type for base table,
It is not necessary to crate table for each entity_type, you can create the table which is really required for you to store the values of that entities and attributes
in this case there is 2 types of entities in entity_type table i.e. “user” and “address”,
now we are going to create a user_entity is a base table to identify each user uniquely
CREATE TABLE IF NOT EXISTS `user_entity` (
`user_entity_id` int(11) NOT NULL AUTO_INCREMENT,
`gender` char(2) NOT NULL,
`signup_date` datetime NOT NULL,
`last_login_date` datetime NOT NULL,
PRIMARY KEY (`user_entity_id`)
) ENGINE=MyISAM;
INSERT INTO `user_entity` (`user_entity_id`, `gender`, `signup_date`, `last_login_date`) VALUES
(1, ‘m’, ’2010-02-06 12:14:06′, ’2010-02-06 12:14:09′),
(2, ‘m’, ’2010-02-07 12:14:18′, ’2010-02-07 12:14:22′);
_______________________________________
here is our user_entity_value table where we can store entity_type, attributes, entity_id(user_id) and value
CREATE TABLE IF NOT EXISTS `user_entity_value` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`entity_type_id` int(11) NOT NULL,
`attribute_id` int(11) NOT NULL,
`entity_id` int(11) NOT NULL,
`value` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;
INSERT INTO `user_entity_value` (`id`, `entity_type_id`, `attribute_id`, `entity_id`, `value`) VALUES
(1, 1, 1, 1, ‘Amol’),
(2, 1, 2, 1, ‘Sananse’),
(3, 1, 97, 1, ‘s.amol@agiletechnosys.com’),
(4, 2, 12, 1, ‘Maharashtra’),
(5, 2, 74, 1, ’422101′),
(6, 2, 15, 1, ‘Nashik’),
(7, 1, 1, 2, ‘Rahul’),
(8, 1, 2, 2, ‘Raikar’),
(9, 1, 97, 2, ‘r.rahul@agiletechnosys.com’),
(10, 2, 12, 2, ‘Maharashtra’),
(11, 2, 74, 2, ’422101′),
(12, 2, 15, 2, ‘Nashik’);
_______________________________________
How to fetch the data from all these tables
select user_entity.*, fname.value as first_name, lname.value as last_name, email.value as email, region.value as region, postcode.value as postcode,
city.value as city, teliphone.value as telephone
from user_entity
left join user_entity_value as fname
on (user_entity.user_entity_id = fname.entity_id and fname.attribute_id = 1)
left join user_entity_value as lname
on (user_entity.user_entity_id = lname.entity_id and lname.attribute_id = 2)
left join user_entity_value as email
on (user_entity.user_entity_id = email.entity_id and email.attribute_id = 97)
left join user_entity_value as region
on (user_entity.user_entity_id = region.entity_id and region.attribute_id = 12)
left join user_entity_value as postcode
on (user_entity.user_entity_id = postcode.entity_id and postcode.attribute_id = 74)
left join user_entity_value as city
on (user_entity.user_entity_id = city.entity_id and city.attribute_id = 15)
left join user_entity_value as teliphone
on (user_entity.user_entity_id = teliphone.entity_id and teliphone.attribute_id = 17)
where 1 group by user_entity_id
select attribute_id from attribute where attribute_code = ‘email’
select user_entity.*, fname.value as first_name, lname.value as last_name, email.value as email, region.value as region, postcode.value as postcode,
city.value as city, teliphone.value as telephone
from user_entity
left join user_entity_value as fname
on (user_entity.user_entity_id = fname.entity_id and fname.attribute_id = (select attribute_id from attribute where attribute_code = ‘firstname’))
left join user_entity_value as lname
on (user_entity.user_entity_id = lname.entity_id and lname.attribute_id = (select attribute_id from attribute where attribute_code = ‘lastname’))
left join user_entity_value as email
on (user_entity.user_entity_id = email.entity_id and email.attribute_id = (select attribute_id from attribute where attribute_code = ‘email’))
left join user_entity_value as region
on (user_entity.user_entity_id = region.entity_id and region.attribute_id = (select attribute_id from attribute where attribute_code = ‘region’))
left join user_entity_value as postcode
on (user_entity.user_entity_id = postcode.entity_id and postcode.attribute_id = (select attribute_id from attribute where attribute_code = ‘postcode’))
left join user_entity_value as city
on (user_entity.user_entity_id = city.entity_id and city.attribute_id = (select attribute_id from attribute where attribute_code = ‘city’))
left join user_entity_value as teliphone
on (user_entity.user_entity_id = teliphone.entity_id and teliphone.attribute_id = (select attribute_id from attribute where attribute_code = ‘telephone’))
where 1 group by user_entity_id
