1

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?

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
alacret
  • 572
  • 4
  • 19
  • 1
    This seems a legitimate and specific enough question about what is *known* to be the implications of choosing either of the two roads described. This *can* be answered free of opinions. Voting to reopen. – Andriy M Jan 03 '14 at 15:32
  • How this data is used? Volume?? (DWH, normal production data with archival etc) – Srini V Jan 03 '14 at 15:48
  • 1
    Can you explain if there is different data stored for a medical activity vs. a logistical activity? e.g. are the columns in those four tables currently the same or very close? – Aaron Bertrand Jan 03 '14 at 15:50
  • @AaronBertrand: `knowing that neither of the subclasses has unique fields` may be the answer but I'm not sure if that is a statement of the fact or a "what if" hypothesis. – Andriy M Jan 03 '14 at 15:57
  • Does "First approach" actually refer to number 2 (you create a super table of activities...)? – Mike Sherrill 'Cat Recall' Jan 03 '14 at 16:09
  • realspirituals: there is little to know about effective data.. this is in design time – alacret Jan 04 '14 at 19:02
  • Aaron: there is no differnt in the field set of boths tables, they have the same columns – alacret Jan 04 '14 at 19:03

1 Answers1

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.

Andriy M
  • 76,112
  • 17
  • 94
  • 154
simon at rcl
  • 7,326
  • 1
  • 17
  • 24
  • but, this prevent me for have some logical constraints in the database level. for example, what if i want some table just be related to MedicalActivities for example, there is no tool for create that type of contraint if i have only one table – alacret Jan 04 '14 at 19:03
  • I didn't suggest that. I suggested either 4 tables (one per activity type) or 5 (one per type plus an Activity table). If you have constraints on e.g. MedicalActivity, then the data item constrained would not be on the 'ancestor' Activity table: by definition it would not be common data. – simon at rcl Jan 04 '14 at 19:13
  • yes, so, i am really seecking for some backup for my idea of having a supertable with childs tables per type – alacret Jan 04 '14 at 19:19
  • Well, that was what my answer was trying to do: do it if there is enough common data between the four activities. – simon at rcl Jan 04 '14 at 19:25