Plugin DB tables on multisite
J.D. Grimes <jdg <at> codesymphony.co>
2015-05-05 14:45:16 GMT
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