0

Most people don't recommend EAV and I know some of the reasons. However what is the difference between an EAV-approach and such an approach?

Table computer: 

id, price, description

Table connections: 

id, name (possible values: LAN, USB, HDMI, ..., all all about 10)

Table connections_computer

comp_id, conn_id

Or is that EAV, too? If yes, what would be a normalized alternative?

Consider, that I want to do searches like that:

All computers, that have BOTH a LAN and a HDMI connection. In this case I would need 1 join / filter attribute, when having it as 1 column / attribute, searching would be easy, but I would have many NULL values.

Any recommendation how to do?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
OnTheFly
  • 2,059
  • 5
  • 26
  • 61

1 Answers1

0

Your example is a plain many-to-many. In the EAV an attribute name is a value of a column, instead of being a name of the column. For example,

computer: {computer_id, attr_name, attr_value} 

insert into computer (computer_id, attr_name, attr_value)
values
  (1, 'connection', 'HDMI')
, (1, 'connection', 'USB')
, (1, 'memory', '2 GB')
;

would be an EAV approach.

Here is an example of the price you pay for the EAV approach (flexibility) in a RDBMS.

Community
  • 1
  • 1
Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71
  • Thanks, so I should use many-many and not 1 column / attribute value? – OnTheFly Aug 07 '12 at 17:03
  • Yes, if you can design -- then design. Meaning that you know your entities and attributes. If you really have to use EAV -- have unknown entries/attributes which are to be defined by users on fly -- then consider document oriented DBs like Mongo, Couch etc.. – Damir Sudarevic Aug 08 '12 at 11:34