WordPress database schema

The funny thing about WordPress database schema is: it doesn’t have any foreign key. Relations and joins are of course possible, but without any foreign keys. Which is quite unfortunate for an Entity Relationship Diagram that builds relations from foreign keys ^^

Luckily Azimutt can extend your database schema using its dedicated language: AML (Azimutt Markup Language). You can, in other things, add the relations which don’t have foreign keys. We did this to avoid cumbersome UI with many inputs and clicks. Instead, you have a very simple language you write in a single input, at your own typing speed. Here is an example of a table defined in AML:

users
  id uuid pk
  group_id fk groups.id
  email varchar unique
  created timestamp=now()

Of course, the full AML documentation is available.

But, back to the WordPress database… With no foreign key, the database integrity rely only on the code, not on usual database mechanism. I can see two reason for this. The first one is for flexibility. They maybe accept the tradeoff to have fewer data quality but also fewer errors. With all their plugin ecosystem it can be a thing. The other one can be for performance. Of course, checking the consistency on each insert or update can be costly. But clearly, this is the first time I see this. If you have other ideas, please let me know.

Another strange thing is, they prefix column names with the table name. For example in the posts table, they have post_name, post_status and post_author. Same in comments with comment_date and comment_author. Not sure why…

The interesting thing is their meta pattern. It allows them to dynamically store additional properties without updating the schema. For example, the posts table has an associated postmeta table which has a link to a post and two important columns: meta_key and meta_value. With this, you can store any key/value you want for any post, without changing the schema. If you have to implement dynamic fields it can be an option. Another one would be to have a JSON column. Each one has its own tradeoffs.