Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <h2>Entity Attribute Value</h2> <p>It's known as the <a href="http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model" rel="nofollow">Entity Attribute Value</a> (EAV) data model, and allows an arbitrary number of attributes to be assigned to a given entity. That means any number of meta-data entries per user.</p> <h2>Why use it</h2> <p>By default there are a few keys that wordpress sets (20 stated in the question) but there can be any number. If all users have one thousand meta data entries - there are simply one thousand entries in the usermeta table for each user - it doesn't have (in terms of the database structure) a limit to the number of meta data entries a user can have. It also permits one user to have one thousand meta data entires, whilst all others have 20 and still store the data efficiently - or any permutation thereof.</p> <p>In addition to flexibility, using this kind of structure permits the main users table to remain small - which means more efficient queries.</p> <h2>Alternatives</h2> <p>The alternatives to using EAV include:</p> <ul> <li>Modify the schema whenever the number of attributes changes</li> <li>Store all attributes in a serialized string (on the user object)</li> <li>Use a schemaless db</li> </ul> <p>Permissions is the biggest problem with the first point, it is not a good idea to grant blanket access to alter the schema of your database tables, and is a (sane) roadblock for many if not most wordpress installs (hosted on wordpress.com or on a shared host where the db user has no alter permissions). Mysql also has a hard-limit of <a href="http://dev.mysql.com/doc/refman/5.6/en/column-count-limit.html" rel="nofollow">4096 columns and 65,535 bytes per row</a>. Attempting to store a large number of columns in a single table will eventually fail, along the way creating a table that is inefficient to query.</p> <p>Storing all attribute in a serialized string would make it difficult and slow to query by a meta-data value.</p> <p>Wordpress is quite tied to mysql, and therefore changing datastore isn't a realistic option.</p> <h2>Further WP info</h2> <p>If you aren't using any/many plugins it's possible you will have a constant number of rows in the usermeta table for each user, but typically each plugin you add may need to add meta-data for users; the number added may not be trivial and this data is stored in the usermeta table.</p> <p>The docs for <a href="http://codex.wordpress.org/Function_Reference/add_user_meta" rel="nofollow">add_meta_user</a> may add some clarity as to why the database is structured that way. If you put code like this somewhere:</p> <pre><code>add_user_meta($user_id, "favorite_color", "blue"); </code></pre> <p>It will create a row in the usermeta table for the given user_id, without the need to add a column (favorite_color) to the main users table. That makes it easy-ish to find users by favorite color without the need to modify the schema of the users table.</p>
    singulars
    1. This table or related slice is empty.
    1. POWhy does Wordpress have separate 'usersmeta' and 'users' SQL tables. Why not combine them?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USAD7six
    1. USAD7six
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POWhy does Wordpress have separate 'usersmeta' and 'users' SQL tables. Why not combine them?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COGreat answer AD7. I would just add, if you write something that adds favorite_color, it may automatically add rows for each defined users if the edit their page even if they dont touch the color field
      singulars
      1. PO
      1. USRyan B
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload