Let's talk about storing additional "duplicate" foreign keys, with an example from the open-source project Krayin CRM.
In the Krayin CRM project, there is a table product_inventories
for which migration looks like this:
Schema::create('product_inventories', function (Blueprint $table) { $table->increments('id'); $table->integer('in_stock')->default(0); $table->integer('allocated')->default(0); $table->integer('product_id')->unsigned(); $table->foreign('product_id')->references('id')->on('products')->onDelete('cascade'); $table->integer('warehouse_id')->unsigned()->nullable(); $table->foreign('warehouse_id')->references('id')->on('warehouses')->onDelete('cascade'); $table->integer('warehouse_location_id')->unsigned()->nullable(); $table->foreign('warehouse_location_id')->references('id')->on('warehouse_locations')->onDelete('SET NULL'); $table->timestamps();});
The warehouse_location_id
field is from a grandparent table. A simplified schema looks like this:
So, we have the location_id
field in both the "child" and "grandchild" tables, duplicating data.
Technically, this violates...