Is it bad practice to create own table for a plugin?

Question

If I want to save settings for my plugin then it is pretty easy and straight forward.

Now I would like to save to the database a bit more.

A file name, and 3 other values that only apply to that file. And there are many files with those values. Is it possible to save kind of subarrays using built in database methods? How can I delete and sort them etc?

Answer 1

I rarely disagree with otherwise knowledgeable users, but in this case I can't help it. In my opinion calling the usage of non-core database tables bad practice per se is just simply wrong.

The choice of whether to go with core tables or add your own depends on several factors.

A query's execution time depends on the size of the table. Hence, if you're planning on storing significant amounts of data, a separate table catering to just this one type of specific data set will inevitably be the more efficient solution.

If you store a lot of regular posts or CPTs alongside these specific data sets, wp_posts as well as wp_postmeta can grow quickly.

For me, this choice ultimately depends on how "posty" the data is. Should it support an author, comments, revisions, excerpts or the like? If so, I'll go with CPTs and/or core functionality. If not, I'll go with separate tables for the sake of resource usage and efficiency.

If Eugene's notion were correct, none of the existing well-written plugins would add their own tables, which fortunately is not the case.

Answer 2

Using WP core DB tables is best practice

1. Using core DB tables makes your data more portable, and easier to backup, since it will be handled by the core exporter/importer, as well as by the myriad backup Plugins
2. Using core DB tables makes your data easier and safer to manipulate, since you will have more intuitive access to the various WordPress core functions related to querying, adding, modifying, deleting, and sanitizing DB data, particularly through the very powerful $wpdb class.
3. Using core DB tables encourages/facilitates best practices for data classification and storage, such as storing Plugin options as an array in a single row in wp_options, and by forcing the Plugin developer to consider carefully the type of data being created/stored - is it a CPT? is it a taxonomy? is it post meta?
4. Your Plugin is less likely to leave behind cruft when using core DB tables.

WordPress provides a means for Plugins to add tables to its database

However, for those use cases where a separate DB table is needed, be sure to use the method that WordPress provides for adding your custom table to the WordPress database, especially so that you can take advantage of the powerful $wpdb class. Note the information/caveats this Codex entry lists:

• Setup information -- user choices that are entered when the user first sets up your plugin, and don't tend to grow much beyond that (for example, in a tag-related plugin, the user's choices regarding the format of the tag cloud in the sidebar). Setup information will generally be stored using the WordPress options mechanism.

• Data -- information that is added as the user continues to use your plugin, which is generally expanded information related to posts, categories, uploads, and other WordPress components (for example, in a statistics-related plugin, the various page views, referrers, and other statistics associated with each post on your site). Data can be stored in a separate MySQL table, which will have to be created. Before jumping in with a whole new table, however, consider if storing your plugin's data in WordPress' Post Meta (a.k.a. Custom Fields) would work. Post Meta is the preferred method; use it when possible/practical.

Thus, we can conclude the following:

1. Storing data (setup, or user-generated) in core WP tables is best practice
2. There are valid use-cases for creating custom DB tables; therefore, creating custom DB tables cannot be considered as an inherent bad practice
3. When creating custom DB tables, WordPress provides a best-practice implementation

Answer 3

Non core database tables are a must if your data is more complex than WordPress post model, it is going to be huge, and it has a lot of meta details which will be searched.

EAV format WordPress uses for its post meta does not lend itself well to multi-criterion search.

If you divide your meta into many entries, you will have numerous entries per post in the post meta table, and searching any post through metas will be much slower.

If you store all metas serialized in an array and have it as only one entry in post meta, this time you will be forced to do only text searches inside that meta, and you wont be able to use comparison operators directly in your sql query.

Not a big problem if your plugin is not going to have thousands of entries and associated meta.

But a major problem if your plugin is going to do anything large.

---

Your situation, a file name as independent entry and 3 meta data entries attached to that entry does not seem so big. You may use wordpress post table and meta table for it.

BUT, if people are going to search for these 3 metas a lot, ESPECIALLY in conjunction, then i'd recommend that you set up separate tables.

With that format just one table with just one entry, which also contains all the metas would be ok, and would query lightning fast.

Incidentally, if you use WordPress tables and also you are using query caching, the user searches for your data would get cached over time and incur less load. But that wouldnt be so prudent as doing separate tables.

Answer 4

You can upload your files into media library. Each item in media library is stored it wp_posts table. It means that each file can have meta data. You can save as much information as you need per each file in wp_postmeta table by using Metadata API.

Is it bad practice to create own table for a plugin?

Yes, it is bad practice to create own table, if you can use core functionality instead.

Answer 5

class TMM {

    public static $options;

    public static function register() {
        self::$options = get_option(TMM_THEME_PREFIX . 'theme_options');
    }

    public static function get_option($option) {
        return @self::$options[$option];
    }

    public static function update_option($option, $data) {
        self::$options[$option] = $data;
        update_option($prefix . 'theme_options', self::$options);
    }

    //ajax
    public static function change_options() {

        $action_type = $_REQUEST['type'];
        $data = array();
        parse_str($_REQUEST['values'], $data);
        $data = self::db_quotes_shield($data);

        if (!empty($data)) {
            foreach ($data as $option => $newvalue) {
                if (is_array($newvalue)) {
                    self::update_option($option, $newvalue);
                } else {
                    $newvalue = stripcslashes($newvalue);
                    $newvalue = str_replace('\"', '"', $newvalue);
                    $newvalue = str_replace("\'", "'", $newvalue);
                    self::update_option($option, $newvalue);
                }
            }
        }
        _e('Options have been updated.', TMM_THEME_FOLDER_NAME);
        exit;
    }

    public static function db_quotes_shield($data) {
        if (is_array($data)) {
            foreach ($data as $key => $value) {
                if (is_array($value)) {
                    $data[$key] = self::db_quotes_shield($value);
                } else {
                    $value = stripslashes($value);
                    $value = str_replace('\"', '"', $value);
                    $value = str_replace("\'", "'", $value);
                    $data[$key] = $value;
                }
            }
        }

        return $data;
    }

}

---

• Name of class is original, rename it as you wish.
• In functions php add: add_action('init', array('TMM', 'register'), 1);
• And add for ajax: add_action('wp_ajax_change_options', array('TMM', 'change_options'));
• To get option where you need use this (for example): $logo_img = TMM::get_option('logo_img');
• Use it to save your options by native wordpress methods