database schema overthinking

ok, I've tried typing this out numerous times trying to see if I can simplify things, but I'm giving up.

Background info: I have a roleplay/roster setup. It contains Fleets,Wings, and Squadrons. (Pseudo military). A Fleet, may/may not have wings, a fleet may have squads. Squads must belong to either a fleet or a wing. Wings are optional. all three units share some common factors. ID,Name,Title,Description,CO,XO,SO (Commanding officer, second, third). Wings and Squads will have a parent. and a squad will have a number of slots for total users. As silly as it sounds, a under can be all three at once in all three units. I'll have RBAC setup so it won't matter. This is mainly for record keeping/roleplay information.

Since a user can be in multiple units and spots, I have a positions table setup to record the user id, the unit id, and any other relevant info.

Questions I have are (in no order):

Will this work?
Should I split the units up into 3 separate tables (fleets, wings, squads), with 3 different models to govern them?
Is there an easier way to do this?

Thank you for any help you can provide!

table users {
  id bigint [pk]
  name varchar [not null]
}

table units {
  id bigint [pk]
  name varchar [not null]
  title varchar [null]
  description text [null]
  co integer [null, ref: > users.id]
  xo integer [null, ref: > users.id]
  so integer [null, ref: > users.id]
  type enum [not null]  //fleet,wing,squad
  spots integer [null]
  unit_id bigint [null, ref: > units.id]
}

table positions {
  id bigint [pk]
  user_id bigint [not null, ref: > users.id]
  unit_id bigint [not null, ref: > units.id]
  title varchar [null]
  spot integer [null]
}

JohnBraun

7 years ago

Level 33

Based on your requirements as listed, I would suggest the following - Laravel specific - approach:

"It contains Fleets, Wings, and Squadrons."

Sounds to me like three separate models, each having their own database table.

"A Fleet, may/may not have wings, a fleet may have squads."

Take a look at Laravel's relation ships. Jeffrey also has a nice series on this topic here at Laracasts: https://laracasts.com/series/eloquent-relationships/episodes/1.

"Squads must belong to either a fleet or a wing."

This calls for a polymorphic relationship.

"Wings are optional. all three units (Fleet, Wing, Squadron) share some common factors: ID, Name, Title, Description, CO, XO, SO"

Just add them to the appropriate database migrations for the Fleet, Wing and Squadron tables, for example:

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateWingsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('wings', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name');
            $table->string('title');
            $table->text('description');
            $table->unsignedInteger('CO');
            $table->unsignedInteger('XO');
            $table->unsignedInteger('SO');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('wings');
    }
}

For the CO, XO and SO references, you'll need to implement the appropriate relationship.

[...] and a squad will have a number of slots for total users.

I don't know exactly what your implementation will be for "slots for total users", but I guess you can rely on a many-to-many relationship between Users and Squadrons. Check out the many-to-many relationship which requires a pivot-table.

// Squadron.php

public function users()
{
    return $this->belongsToMany(User::class);
}

I hope I could have helped you somewhat, otherwise let me know.

1 like

ThePoet444

7 years ago

Level 20

Thank you for your response!

I wasn't worried about the relationships, I was more concerned if simplifying the three units into one table was a wise thing. All three share the same common setup with minor differences, so I was thinking to make things easier to have one table and one model. Having 3 models that share the same commonality might cause me to reuse code. Which was my rational for avoiding it. Still, in the end it might make things easier to have 3 tables/models.

jred_lewis

7 years ago

Level 36

@THEPOET444 - If your models do reuse the same methods, then you could extract those methods into a trait that you could use.

Please or to participate in this conversation.