Any potential downsides to either of these subclassing approaches?

Question

Imagine that you have several tables that stand for activities:

• ProcessActivities
• MedicalActivities
• MaintenanceActivities
• LogisticalActivities

You now realize that all are activities, then, what would you do?

1. you create a master table named "activities" and you add a type field for the values: process, medical, maintenance, logistical, or
2. you create a super table of activities, and one table for each subclass with only the primary keys, knowing that neither of the subclasses has unique fields

I can think some several pros/cons that get involved in the decision:

First approach:

1. CRUD operations in an activity will imply to work in two different tables
2. Simplify the E-R diagram
3. Lose of integrity constraint

Second approach:

1. The two indexes to maintain this relations would be kept in two different places
2. Semantic

Are there any potential performance or other consequences in either of those approaches?

Answer 1

I would only go with a master Activity table of there is data common to all four activity types which can be split out into it. This would also need an ActivityTypeId indicating which type of Activity it is and so which table to look for details in.

Assuming that there is common data:

• Pros: proper abstraction of data into common types out of more detailed types.
• Cons: slightly more complex coding but not too much

Assuming that there is minimal common data:

• Pros: None really
• Cons: improper abstraction of data with a unified whole (an xActivity) spread out in 2 places instead of one.

How much common data is required? This is where your experience and judgement comes in. You also need to factor in things like: will there be an enquiry screen which lists out all Activities whatever the type? If so, can the columns in this query be placed in a single table without mangling the data (e.g. for one column activity type 1 has a string which type 2 has a date etc)?

No easy answer I'm afraid.