I am trying to design a reports database that allows you to create different types of reports. As such, I have my main reports table (I am creating as a Laravel migration).
Schema::create('reports', function (Blueprint $table) {
$table->increments('id');
$table->string('report_name');
$table->string('status')->nullable();
$table->integer('removed_count')->default(0);
});
So a report can be one of 3 different types of reports. As such, I have a report_types table
Schema::create('report_types', function (Blueprint $table) {
$table->increments('id');
$table->unsignedInteger('report_id');
$table->string('report_type');
$table
->foreign('user_id')
->references('id')
->on('reports')
->onDelete('cascade');
});
So a report will have a one to one relationship with a report type. In essence, when you create a report, you either create Report A, Report B or Report C.
This is where I am confused. Each report type requires the capturing of different data. So for instance, if they select Report A, I may need to capture the following.
Schema::create('report_a_data', function (Blueprint $table) {
$table->increments('id');
$table->unsignedInteger('report_id');
$table->string('cust_id');
$table->string('col1');
$table->string('col2');
$table->string('col3');
$table
->foreign('report_id')
->references('id')
->on('reports')
->onDelete('cascade');
});
If they select Report B, the data I need to capture is different. So my question is do I create a table for each report data I need to capture? So I will have a table like the above for each report? If I do this, does it make sense having a report_types table? And with the table above, should it link directly to the reports table?
Any advice on how to properly structure this would be greatly appreciated.
