ideas about an EAV model

I don't order my ideas after preference - more after they relate to each other - preference will be done later
But because EAV is a well defined term with well defined semantics i'm using entity, attribute and value extensively and when i haven't forgotten it mark them with underline to underline their meaning

• classic EAV model - one table defines an entity that groups all attributes which define the name and type of the value

  Comparison

  disadvantage: quite complex - human side and mysql side so also performance not very well, error prone
  advantage: very dynamic,extensible and with a good api it can feel like a normal table
  human understandable: terrible without tools
• Anonymous table (entity) consisting of, for example, 6 anonymous rows with datatype text,int,varchar and name val1,val2..
  The meaning (attribute) will be defined somewhere else and the value can be stored very easy inside
  
  

  Comparison

  disadvantage: not very dynamic.. limited to a predefined maximum of attributes
  memory overhead of unused columns
  values will be a bit obscured for a human - but not so much
  advantage:easy to implement and fast at all mysql-operations
  feels native
  human understandable: ok
• Dynamic Anonymous table (entity) - like above but with a dynamic width (tables will be created at runtime - after what will be needed) - so if only 2*varchar is needed a table with 2*varchar is created.. when other data needs other sizes it will create other tables
  

  Comparison

  disadvantage: values will be quite much obscured for a human - cause finding one of the many tables
  extensibilty: what happens if the user needs 3*varchar after he populated a 2*varchar
  metadata is in many different tables:
  • primary key isn't unique across all tables (only when used together with transactions)
  metadata like userid must be in each table.. if in future new metadata will be added this must be done for each existing table
  advantage:fast at all mysql operations - when working locally (just with a single table) it feels native
  human understandable: bad
• JSON datafield - so all attributes will be json encoded.. this can be either done everytime as key:value storage or a onetime keystorage as array and then multiple arrays of values [key1,key2] and later data is [val1,val2] or even (but with this aproach we actualy could start throwing away the db) [[val1,val2],[val1,val2]]
  It's obvious that the key:value thing will be very dynamic and not bound to an entity (using still the eav-terms) cause each value is it's own entity too
  

  Comparison

  disadvantage: searching, selecting and reordering will be very slow.. since all fields must be parsed and json_evaled
  advantage: very dynamic
  human understandable: good - not so long json looks ok.. but also a small javascript helper could do the job - searching will be again a pain
  
• attribute+value table with prototyping behaviour - so the table will mostly look like "key, type, value" - the type isn't realy needed it will specify wether it is int,bool,enum(option1,option2..) or something like that - key will just be the attribute and value is the value
  the entity will be fully described by a predefined set of rows (maybe just set the first one as dummy values)
  

  Comparison

  disadvantage:overhead through storing the key and type
  metadata should be stored somewhere else to reduce overhead
  advantage:prototyping is quite dynamicly efficient and funny - see javascript
  adding new entities can be easy depending on the way how the prototype is determined
  human understandable:when plainly updating a row it will be easy - adding a new one is hard since we have to find the prototype.. but could be ok if no existing keys won't throw exceptions
  
• couchdb - a database which just uses the eav pattern and allows this easily

  Comparison

  right now I don't know much about it so perhaps this is biased
  disadvantage:probably no native yii-support
  2 different databases since I like to use yii for models and so on -> no joins possible
  doesn't run everywhere (but also this isn't so important
  advantage:new technology
  standardized interface
  very dynamic
  human understandable: very good since there exists documentation and tools
• below that line are some no goes
• hyper dynamic table creation
  basically each entity just will create it's own table - like a static eav..

  Comparison

  this sucks cause of all disadvantage of the Dynamic Anonymous table multiplied with the amount of new tables (since all disadvantages are related to many tables - and this idea realy will create many tables)