Let us continue next part of EAV data model article. EAV/CR has traditionally meant entity-attribute-value with classes and relationships.EAV/CR is a data modeling technique that, with good amount of metadata, executes a logical database schema and is having different physical database schema. Logical tables are usually prepared from one or many physical tables that often has single column of real payload data of a specific data type like string, integer. All logical tables (that is “classes”) are related to each other by a uniform physical relation system defined and managed by metadata.
The supermarket has hundreds of products in their stock, with new products being added on daily basis and others taken out from sale. When customer purchases 3 products the invoice lists details of products actually bought, one item per tuple. The invoice does not contain each item in the supermarket that customer could have bought, so the customer’s invoice is sparse.
In database terminologies, the entity is the sales invoice, with added information(metadata) like transaction id, date and time, supermarket location, etc. Each detailed row in the invoice resembles to a record in sale line table and saves an attribute and many values. In this situation, an attribute is a item bought by the buyer. The values are amount, unit price, concession and whole charge.
The above illustration states the sparseness of attributes (a buyer purcahses only a selection of the accessible items) and adds a new term: row modelling. The table is row modelled where the series of facts relating an entity are noted as multiple rows. Each new-fangled set of facts is kept in database as supplementary rows rather than supplementary columns.
Row modelling is a typical data modelling method in designing databases. It should only be applied where couple of circumstances are encountered:
• the data for a specific entity is sparse
• the data is susceptible to change
Row modelling is unsuitable where sparseness and unpredictability don’t exist, in such case conventional column modelling must be applied.
An entity can be any element. Entities in EAV data model are accomplished via an Objects table that seizures mutual facts about every element, like name, explanation, etc. The Objects table should have exclusive identifier for every entity, and this is often inevitably produced. The identifier is then applied across the database as a foreign key.
Attributes are saved in a devoted attributes table. The primary key of this table is used as a reference across the database. The attributes table is often accompanied with numerous metadata tables that define an attribute in more detail. This metadata information is usually applied to systematize production of user interfaces for glancing and expurgation data.
The easiest answer to signify data in an EAV data model is to save it as a string. However, this method is moderately ineffective since it needs data type conversions when doing any operations with values. Moreover, indexes on values saved as strings do not permit enhanced range hunts for numeric and date types; this is a usual issue when working with key-value pairs data of mixed data types.
To enhance performance in such circumstances, the EAV data model uses distinct tables for every data type. Attribute metadata recognizes the accurate data type and subsequently the EAV table in which data is stored. This method is much more effectual as it permits metadata caching for a given set of attributes in a form preceding to access the data.
One main disadvantage of this answer is seen when the data type of an attribute wants alteration This needs reassigning data from one table to other, which is troublesome, and can be achieved using stored procedures.
Generally, values that are vacant or do not apply to this particular entity are not saved in the EAV model. In some situations, there is a requirement to capture reasons for lost values. In such cases the answer is to augment a lost value code column to a table, which is non-null only when the value column is null. This code is then applied to look up a list of textual details.