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.
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:
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.
