1

Topic: EAV model

All good evening;
Under the task it is necessary to implement more than 10 tables with a different amount and types of fields. I thought and made in the form of EAV as follows:

-------------------------------------------------------
create table entity (id number (10)
,parent_id number (10)
,name varchar2 (255));
--
insert into entity (id, parent_id, name) values (1, null, ' Factory ');
insert into entity (id, parent_id, name) values (2, null, ' Mark of the machine from factory ');
insert into entity (id, parent_id, name) values (3,1, ' Factories of Russia ');
insert into entity (id, parent_id, name) values (4,2, ' Characteristics of the machine VAZ 2101 ');
-------------------------------------------------------
create table entity_attr (id number (10)
,entity_id number (10)
,att_type char (1)
,name varchar2 (255));
--
insert into entity_attr (id, entity_id, att_type, name) values (1,4, ' V ', ' the Amount of horses ');
insert into entity_attr (id, entity_id, att_type, name) values (2,4, ' V ', ' Colour of the machine ');
insert into entity_attr (id, entity_id, att_type, name) values (3,4, ' V ', ' the Amount of doors at the machine ');
-------------------------------------------------------
create table entity_attr_value (id number (10)
,attr_id number (10)
,str_value varchar2 (1000));
--
insert into entity_attr_value (id, attr_id, str_value) values (1,1, ' 333 ');
insert into entity_attr_value (id, attr_id, str_value) values (2,2, ' Red ');
insert into entity_attr_value (id, attr_id, str_value) values (3,3, ' 5 ');

1. Who  faced such tasks? It would not be desirable to produce 10 new tables as fields of tables and their amount can change. What reefs are in EAV?
2. How it is possible such table ?
3. It is better to store all in  or in entity_attr_value it is necessary to add instead of str_value three fields for example str_value, number_value and date_value?
If it is possible to divide  links.
Thanks.

2

Re: EAV model

cobalt_frog;
If life without eav is not lovely, take nosql.

3

Re: EAV model

and  wrote:

cobalt_frog;
If life without eav is not lovely, take nosql.

Yes not that that it is not lovely... Simply after I made it under this circuit there is a thought, and whether costed?) All the same plane tables to me seem more preferable , and a dog with it that it is necessary to create new 10 tables. It would be desirable to learn judgement of participants of a forum about it.

4

Re: EAV model

cobalt_frog wrote:

It would be desirable to learn judgement

EAV in search it is not driven in?

5

Re: EAV model

+ It would be desirable to learn, who from  used the given approach at the decision of tasks, what difficulties faced and as them solved)

6

Re: EAV model

cobalt_frog wrote:

after I made it under this circuit there is a thought, and whether costed?

Usage EAV demands slightly other style of thinking in comparison with
The square-cluster. So if you feel discomfort, means EAV really not
It was necessary to do.

7

Re: EAV model

cobalt_frog;
EAV it is expedient to use as a last resort, for example, when for the goods description the variable dial-up of attributes (the goods 1 is used: a vest dark blue, a line white, a material a clap, a print of Pokemony; 2: macaroni weight of 0.5 kg, vendor Makfa, composition: solid sorts of wheat etc.). . even if you work with the same goods, at which same dial-up of attributes I to you strongly do not recommend to use EAV. The big minus at such structure: requests and dml-operations occupy more time, than with the normal table in normal 3 to the form.

wrote:

it would not be desirable to produce 10 new tables

Here it at all the base for usage EAV sad. Read at least in Wikipedia the description, your example shows as it is not necessary to project structure.

8

Re: EAV model

cobalt_frog wrote:

2. How it is possible such table ?

It is impossible to answer this question. You should approach to business and ask. What volumes will be? What
Retrieval operations will be? What SLA stand? How many  agree to wait, etc.
Will analyze then what their these operations to affect what fields. Probably
There will be two sets of fields. One -  (normal plane tables). Indexed.
The second set - will lie in EAV as addition. Everyones there go descriptions, comments, and is rare
Used type attributes "___" e.t.c. Then to construct a breadboard model
And to run on it the requirements.
It is possible to do your problem and without these breadboard models and requirements. But there will be a risk that dispute
God is reduced to theological questions of type "is uniform or a Trinity?" Or "how many angels can stand...?"
Etc. rhetorical disputes where are not present any criterion of the validity and there are simply private judgements.

9

Re: EAV model

mayton wrote:

it is passed...
It is impossible to answer this question. You should approach to business and ask. What volumes will be? What
Retrieval operations will be? What SLA stand? How many  agree to wait, etc.
Will analyze then what their these operations to affect what fields. Probably
There will be two sets of fields. One -  (normal plane tables). Indexed.
The second set - will lie in EAV as addition. Everyones there go descriptions, comments, and is rare
Used type attributes "___" e.t.c. Then to construct a breadboard model
And to run on it the requirements.
It is possible to do your problem and without these breadboard models and requirements. But there will be a risk that dispute
God is reduced to theological questions of type "is uniform or a Trinity?" Or "how many angels can stand...?"
Etc. rhetorical disputes where are not present any criterion of the validity and there are simply private judgements.

Volumes - ten thousand records a month to wait agree though second, under this data there will be normal outswappings in Excel, change of record in subsequent, search on what or to a field (attribute) is possible.
It is possible more in detail apropos:

mayton wrote:

it will be possible two sets of fields. One -  (normal plane tables). Indexed.
The second set - will lie in EAV as addition. Everyones there go descriptions, comments, and is rare
Used type attributes "___" e.t.c. Then to construct a breadboard model
And to run on it the requirements.

If it is possible the link. Thank you very much.

10

Re: EAV model

The link I will not give. It is simple from personal experience. And you should result examples of retrieval requests.
In business setting language. "To find all machines with 4 doors and capacity not less..." Etc.
And the more - the better.

11

Re: EAV model

mayton wrote:

the Link I will not give. It is simple from personal experience. And you should result examples of retrieval requests.
In business setting language. "To find all machines with 4 doors and capacity not less..." Etc.
And the more - the better.

To find all machines with 4 doors
To find an amount of all machines of red color with an amount of doors <4
To find date of the very first model of the machine
To find cost of all machines with creation date <2000
Interests how to organize two sets of fields ( and eav) for an example which I resulted.
Thanks.

12

Re: EAV model

to mayton wrote:

It would be desirable  to see an example of the mixed structure (relational and eav).

13

Re: EAV model

Well that's all. Doors, you transfer cost and date of creation to the plane table. Also you build indexes (on requests certainly).
And all remaining attributes - in EAV.

14

Re: EAV model

... And then appear new  and "carefully thought over " goes to stuff.

15

Re: EAV model

Basil A. Sidorov wrote:

... And then appear new  and "carefully thought over " goes to stuff.

will be, some of fields if only eav to use it can dobavljatsja/be deleted dares simply creation of new essence and new attribute composition for it, if the mixed model there are problems.

16

Re: EAV model

Saw system when at first all made in EAV, then for search made View atop EAV, then for acceleration View replaced with tables and the system parallely stored the data both there and there, then made a setup option how to store: EAV + the table or it is simple in tables)))
Here such there was an evolution. On the other hand, initial choice EAV allowed to make quickly the first version of system, and further it already developed...

17

Re: EAV model

cobalt_frog wrote:

if the mixed model there are problems.

About that and speech.
At EAV the problems suffice to add also strangers.

18

Re: EAV model

Basil A. Sidorov wrote:

At EAV the problems suffice to add also strangers.

In my opinion at EAV not problems, and patrimonial traumas.
While EAV arranges - all well. As soon as normal/difficult search + normal/difficult reports from EAV is necessary it is necessary to leave. Well or remaining on EAV to create thousand lines of a shit-code which is written difficult and on servers all processors and storage .... As soon as processors and storage come to an end.... From EAV it is necessary to leave)))
AFAIK
BUT on approaching/simple tasks EAV certainly can simplify coding and add scalabilities. Though, it is not so clear, why then instead of EAV simply not to use Dinamic SQL and not to make the elementary GUI the interface to ALTER TABLE... ADD COLUMN. For systems 24*7 it certainly hardly approaches, but for 99 % of remaining systems - quite.
IMHO

19

Re: EAV model

Leonid Kudryavtsev wrote:

it is passed...
AFAIK
BUT on approaching/simple tasks EAV certainly can simplify coding and add scalabilities.
IMHO

For example, storage of meta data.

20

Re: EAV model

Leonid Kudryavtsev wrote:

it is passed...
In my opinion at EAV not problems, and patrimonial traumas.
While EAV arranges - all well. As soon as normal/difficult search + normal/difficult reports from EAV is necessary it is necessary to leave. Well or remaining on EAV to create thousand lines of a shit-code which is written difficult and on servers all processors and storage .... As soon as processors and storage come to an end.... From EAV it is necessary to leave)))
AFAIK
BUT on approaching/simple tasks EAV certainly can simplify coding and add scalabilities. Though, it is not so clear, why then instead of EAV simply not to use Dinamic SQL and not to make the elementary GUI the interface to ALTER TABLE... ADD COLUMN. For systems 24*7 it certainly hardly approaches, but for 99 % of remaining systems - quite.
IMHO

If you are not able to work with EAV, it does not mean that problems at it.
GUI the interface to ALTER TABLE... ADD COLUMN is a delirium. A maximum - for student's hand-made articles, type "look as I be able!".
Dinamic SQL possesses not a smaller amount of lacks, than EAV. Especially if to use it pretty often.
people write, therefore as not in a state to construct normal architecture, do not distinguish OLTP and DWH, well and do not understand, where there is a sense in EAV and where is not present.

21

Re: EAV model

Adx wrote:

GUI the interface to ALTER TABLE... ADD COLUMN is a delirium. A maximum - for student's hand-made articles, type "look as I be able!".

Provisionable systems are very often necessary. For example:
www.kamis.ru (alter table)
The unit of Quality from OeBS
Descriptive Flex Fields from this OeBS
Case in Oracle Utilitites. Customer Care and Billing (all in xml)
That from them more "a student's hand-made article", I do not know. A variant to make through alter table, personally it is pleasant to me more that perversions with XML, EAV and other.
How to "prepare" EAV that on it there would be a normal search - personally I do not know. To write search for EAV (c the registration or, and etc...) it is possible.... But the construction from SELECT' and UNION' will be . IMHO and AFAIK

22

Re: EAV model

Here only one minus, about any modern means of type Hibernate it is better to forget)))
Well or it is necessary not fig  to mold, it is absolutely not clear for the sake of what.
For example Oracle CC&B: Meta data => anotatsija-generation =>  => xml-generation on  => analysis xml => json - xml - dto - copy book' - dto - xml - json. There, here it is reverse, to you and me pleasantly (C)))) well and programmers in Manila are occupied by operation (copy book' are written manually!). But Hibernate and XML to the utmost)))
AFAIK

23

Re: EAV model

Leonid Kudryavtsev wrote:

it is passed...
Provisionable systems are very often necessary. For example:
www.kamis.ru (alter table)
The unit of Quality from OeBS
Descriptive Flex Fields from this OeBS
Case in Oracle Utilitites. Customer Care and Billing (all in xml)
That from them more "a student's hand-made article", I do not know. A variant to make through alter table, personally it is pleasant to me more that perversions with XML, EAV and other.
How to "prepare" EAV that on it there would be a normal search - personally I do not know. To write search for EAV (c the registration or, and etc...) it is possible.... But the construction from SELECT' and UNION' will be . IMHO and AFAIK

I do not say that so do not do. Idea quite obvious. GUI is, ALTER is why not to unite?
But as a whole it or from a hopelessness (cases different happen), or from inability to find alternative.
But as changeover EAV is a delirium.
In ORACLE at function PIVOT any problems? Not so it is a lot of with it worked (

24

Re: EAV model

[quote =-2] it is passed...
Axapta so it is made.

At EAV there is one very serious lack - it needs to be able to use. All anything while system write the pro but as soon as it comes into the hands  - starts to climb . With plane tables to work is easier , and frequently it outweighs all possible advantages. If you are assured that with system will work... ... Not so qualified developers it is better EAV not to use. Dares creation of a kernel round model, a kernel write the pro,  - , but it is not always possible. It is especially problematic, if you write system for indirect developers.