CODEWITHSUNDEEP
phplaravelmariadb
Maximi
Maximi

Reputation: 569

MariaDB / Laravel 5 best engine for storing tags

Hello i want store tags in db.

The searching for posts will be based on tags. Then i am searching something for: consistency and performance Unfortunately it will be nice have foreign keys or some alternatives for it. I try it with Aria engine, but this don't support foreign keys.

Migration file looks like:

    Schema::create('tags', function (Blueprint $table) {
        $table->engine = 'Aria';
        $table->increments('id')->nullable();
        $table->string('tag',100);
        $table->unique('tag');
    });

    Schema::create('post_tag', function (Blueprint $table) {
        $table->engine = 'Aria';
        $table->integer('post_id')->unsigned()->index();
        $table->foreign('post_id')->references('id')->on('posts')->onDelete('cascade');
        $table->integer('tag_id')->unsigned()->index();
        $table->foreign('tag_id')->references('id')->on('tags')->onDelete('cascade');
        $table->primary(['post_id', 'tag_id']);
    });

Upvotes: 2

Views: 372

Answers (1)

Rick James
Rick James

Reputation: 142518

I suggest that "tags" do not need FOREIGN KEYS. You will have one place where you insert tags; you will get that code right and won't need the costly checking all the time.

Instead of having a CASCADING DELETE, just leave excess rows in Tags. Please spell out the SHOW CREATE TABLE and SELECTs you envision. From those, we can better discuss the merits of first the schema, and only second which Engine to use.

Use INSERT IGNORE or INSERT .. ON DUPLICATE KEY UPDATE .. to insert, without dups, tags.

Removing no-longer-wanted tags is more challenging; FKs cannot achieve such. (Hence, another argument for simply leaving them in place.)

If there is nothing more than a tag, it may not be worth it to normalize tags.

Upvotes: 1

Related Questions