I have a database which stores the academic activities in which some individuals or departments are involved. An activity could be any among some of these types:

  • article (written)
  • course (imparted)
  • book (written)
  • prize (received)
  • project (developed)
  • ...

and can involve a whole department (as an entity) or individuals (1 to n). It's a XOR, or departments or individuals, e.g.:

  • an article is written by 1:n individuals, never by a department.
  • a prize is received by a department, never by individuals.
  • ...

Each Activity is defined by multiple properties: some are common to all types, some are specific for each type:

  • common properties: title, description, year, valoration, ...
  • specific properties:
    • for article: magazine, month, ISSN, URL,...
    • for course: institution, city, hours, course_type, ... ...

¿What is the proper way to design the database? I came up with two ideas:

A) Isolate each type in one table with all columns it needs ( common and specific ). Each table manages its own ID, non unique between all activities.

enter image description here

Each table has its own relationship table with individual OR department.

B) One "master" table for common columns and primary ID, and one "child" table for the specific columns of each activity type:

enter image description here

Everything is shared unless its own specific columns.

Option a) seems proper way to do it, option b) seems ugly to me.

But in real world I prefer b). If I need a list of all activities in year 2017, a simple query to master is enough. If later I need a concrete activity, consulting very few tables I get all info (common + specific columns, individuals or department involved, etc).

¿Is a bad practice to design the database thinking of my app requirements? I need some advice here.

  • 1
    You may find of help answer a, answer b and answer c; they are all about scenarios involving a conceptual structure known as supertype-subtype. – MDCCL May 4 '18 at 14:38
  • 1
    Thank you @MDCCL, that is what I was looking for. I just did not know how to name it. :S – raguchi May 7 '18 at 7:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.