[wp-hackers] Plugin DB tables on multisite
jdg at codesymphony.co
Tue May 5 14:45:16 UTC 2015
I'm building a feature for a plugin where some items are going to be stored in a database table. These items have metadata, but I haven't decided whether that will be stored in a separate table or just serialized in a column. The main point is that we'll have a table of items, and it will be supporting multisite.
There are two ways (maybe more?) that I could create the database schema:
I could have one global table for the entire network, with a site_id column which would be used to associate each item with a particular site (kind of like the user or usermeta tables). Or,
I could create a separate table for each site (like the posts tables).
I'm wondering if anyone has any advice to offer as to which of these options I should choose.
Other things to keep in mind:
There might be as many as 100 items per-site, maybe more rarely, but usually less.
The items are strictly associated with a specific site, and don't presently need to be read by other sites (though perhaps there is a small chance that could change in the future). However, when the plugin is network-active, there is support for network-wide items, too.
Though the plugin supports network-activation, I suppose if I use separate tables for each site I could create them lazily on-the-fly, instead of all at once when the plugin is network activated.
The tables that the plugin currently uses are already designed like #1 above, though some of them actually do need to be queryable across sites, so it is a slightly different case.
I've briefly considered making these items a post type, and so they'd just be stored in the post tables. However, I'm not sure how well that will work. The main problem I see is that there would be no support for network-wide items.
More information about the wp-hackers