[wp-hackers] Plugin DB tables on multisite

[Jerry Milo Johnson]

I would keep it as close to wp-like as possible.

if you need a table, make it match that blog's table structure.

wp_1_mytable.

If you ever need to split, or export, or move that subblog to a different
network, or on its own, it has its own set of tables and isnt co-mingled.

If possible, and the data fits (or sort-of fits) into a custom post type,
with main data, title, id, date, categories and metadata, you should do so.
it is understandable, and a lot of the heavy lifting is done for you.

If you need to store "global" custom post types, I typically place them on
blog 1 (the main site blog), alongside the main blogs cpt entries, but have
a metadata flag for "global", and use switch-to-blog to pull them out when
I need them.



On Tue, May 5, 2015 at 10:45 AM, J.D. Grimes <jdg at codesymphony.co> wrote:

> Hi hackers,
>
> 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.
>
> Any thoughts?
>
> -J.D.
>
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>