How do we handle database schema with artwork types which unique has data for each type?

Question

artwork.jpg

I am trying to develop an artwork solution but stuck on the category type issue on how to code this into a database schema. I just started to understand the concept of parent and foreign key.

An artwork belongs to a category type like photos, musical scores, paintings, sculptures. What I did was created a table for artwork and a table for category types and linked foreign key to the main artwork table. What if photos has a specific or unique information like photo format and photo date taken. In an attempt to normalize this, do we need a separate table to accommodate the photos information?

How do I convert the diagram schema?See the attached artwork.jpg for the diagram. That I not only link the artwork table to categories, each category type will also have a separate table to bring in unique information like photo format for photos, composer name and length(minutes) for musical scores. Please help shed light on this. Thank you.

 

public function up()
    {
        Schema::create('artwork', function (Blueprint $table) {

           

            $table->increments('id');
            $table->string('title');

            //this is foreign key to the categories table
            $table->integer('category_id')->nullable()->unsigned();
            $table->foreign('category_id')->references('id')
                  ->on('categories')
                  ->onDelete('cascade');
            
            $table->string('author');
            $table->string('groupcountry');
            $table->string('year');
            $table->string('description');
          
        });
    }

=============================

class CreateCategoriesTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('categories', function (Blueprint $table) {
            $table->increments('id');
            $table->string('category_name');
            $table->string('category_description');
            
        });
    }

Answer 1

If you are just beginning to understand the concept of primary and foreign key, then you might be just getting started at relational database design. There's quite a bit to learn, although you can get started with simple cases before you learn a lot of it.

One concept you may wish to learn is the difference between HAS-A relationships and IS-A relationships. An example of a HAS-A relationship might be the relationship between and artist and an artwork (extraneous to your question). HAS-A relationships are present throughout all the tutorials that bring you up to speed on database design.

IS-A relationships are more subtle, and database tutorials often don't present this clearly. An example of an IS-A relationship is the one between photo and artwork. A photo is an artwork. This is typically a one-to-one relationship, and is usually an instance of a class-subclass (type-subtype) situation. That's the case here. photos, musical scores, paintings, sculptures are all subtypes of artwork.

For specific techniques, I suggest you look over the info and the questions grouped under these three tags:

single-table-inheritance class-table-inheritance shared-primary-key