WordPress Database Architecture : the good and the bad



  • So I've heard Wordpress stores everything in one table, WP_POSTS.

    I heard some e-business plugins would insert new items-for-sale records into WP_POSTS also.

    So this practice of inserting everything into one table, what are the advantages and what are the disadvantages?

    Especially regarding Entity Relationship and performance when you have millions of records in the WP_POSTS table.


  • area_deu

    @Ascendant said:

    So I've heard Wordpress stores everything in one table, WP_POSTS.

    You heard wrong.



  • Tour of the WordPress Database

    wp_posts

    The posts table is arguably the most important table in the database. Its name sometimes throws people who believe it purely contains their blog posts. However, albeit badly named, it is an extremely powerful table that stores various types of content including posts, pages, menu items, media attachments and any custom post types that a site uses.

    The table’s flexible content nature is provided by the ‘post_type’ column which denotes if the row is a post, page, attachment, nav_menu_item or another type. But this flexibility also makes it hard to discuss and describe. Essentially the table contains rows of content objects with different types, but for ease of reading, I will refer to the rows as “posts” throughout this article.

    Did I misunderstand? It says custom post types. That could be anything.



  • For all intents and purposes, just train yourself to read wp_posts as json_data and you'll be fine.


  • area_deu

    "Various types of content" != "everything".

    The WordPress database design is actually quite good, especially when compared to shitheaps like Discourse. I wouldn't design a custom application with clear-cut requirements that way, but if you want to keep your platform easily extensible without changing the schema for every plugin that comes along, that's how you have to do it.



  • @ChrisH said:

    "Various types of content" != "everything".

    Well, if you wish to use that operator, then yes of course you would be correct.

    @ChrisH said:

    if you want to keep your platform easily extensible without changing the schema for every plugin that comes along, that's how you have to do it.

    I think it could work nicely too.
    I just wanted to get some opinions from people who know well in database architecture.



  • @AlexMedia said:

    json_data

    Could you elaborate a little?
    Of course, I know and use json all the time in ajax calls but what does it do when it is stored in the database?


  • area_deu

    @Ascendant said:

    I just wanted to get some opinions from people who know well in database architecture.

    Fair enough.
    The database design is solid for what it does. I only hate WordPress because you're forced to use MySQL (and, to a lesser extent, I hate them because PHP).


  • Winner of the 2016 Presidential Election

    It's been a while since I looked at the WordPress source code, but I remember that it didn't look like a well-written application to me. The DB design may be fine, though.


    Seriously, Discourse? I clicked on the reply button below @ChrisH's post, and you insert a full quote of the OP?

    Paging @discoursebot.



  • @‍asdf - Days Since Last Discourse Bug: 0

    <!-- SockBot/2.11.9 (Cheery Chiffon; owner:loopback0; user:discoursebot) 2016-01-18T10:34:09.910Z -->


  • As far as I know (it's been a while since I last touched a Wordpress database), items are stored as serialised objects.


  • mod

    @Ascendant said:

    I heard some e-business plugins would insert new items-for-sale records into WP_POSTS also.

    In Wordpress, you can have "posts" with different "types": for example, a text-based blog entry, or a image-based entry in a gallery-style blog, or some such.

    Ecommerce plugins overload that to make a post type like "item", which then has properties like price, image, et cetera.



  • @Yamikuronue said:

    In Wordpress, you can have "posts" with different "types": for example, a text-based blog entry, or a image-based entry in a gallery-style blog, or some such.

    Ecommerce plugins overload that to make a post type like "item", which then has properties like price, image, et cetera.

    Pretty much yes.

    WP_Post contains anything that will come out of the DB in getposts query. There's a post_type, which defines what kind of item it is-- like attachment, post, post. Revisions to such are also stored there.

    Not everything is stored there. Comments, users, and meta stuff (tags, and such) are in their own tables.

    So if you wanted to create a new "thing", you would create a Post entry with whatever post_type you want, then query later only for that post_type. (default post type is, of course, "post").

    If you're dealing with millions of items, you probably have to create some custom SQL index to suit your needs.



  • @Ascendant said:

    So I've heard Wordpress stores everything in one table,

    SharePoint does something like that, per site collection if I'm not mistaking.



  • @Ascendant said:

    So I've heard Wordpress stores everything in one table, WP_POSTS.

    That's not true. Wordpress is free; you can easily install it and check what its DB schema looks like.

    @Ascendant said:

    So this practice of inserting everything into one table, what are the advantages and what are the disadvantages?

    The question is probably moot since it's based on an untrue assumption.



  • @Ascendant said:

    Did I misunderstand? It says custom post types. That could be anything.

    ... but they're all pieces of content that WordPress turns into pages. That's the important thing.

    You said "anything". "Anything" includes, say, users? (Nope, not in there.) Categories? (Nope, not in there.) List of plug-ins? (Nope.) etc.

    The confusion is the table is named "Posts" but also includes content items like pages or file uploads. That's just a result of the product growing over time, it's not a WTF.


  • Winner of the 2016 Presidential Election

    If by "growing" you mean "everyone using as a dumping ground so the shitty Loop can become Skynet", you were correct.



  • No, that's not what I meant.


  • Winner of the 2016 Presidential Election

    And you complain about the rest of the forum not having a sense of humour.



  • "fixed that for you" jokes are not funny.



  • @blakeyrat said:

    "fixed that for you" jokes are not funny.

    ....................................................................................................... next time


    Filed under: <del>not</del><ins>lol</ins>


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.