Post reply

Database design - inheritance, interface

  • February 11, 2009 at 5:02 am

    #197026

    I am designing DB for tailored project management system. I'm in doubt how to solve "multiple parent" problem. On the attached picture, there is Work Breakdown Structure (WBS), starting from Work Order on the top, which can have several Jobs (master-detail), and then WBS has two branches (supertype/subtype). If ExternalProject flag in Job table is on, then I should have left branch (AssistanceProject, AssistanceGroup, AssistanceTask), otherwise it is right branch (Group, Subgroup, WorkTask). There is also one small table representing Absences (vacancy, sick leave, etc...).

    I would like to have Timesheet table (on the very bottom) where TaskID will have multiple parents. That is obvious problem. What is your suggestion/experience ?

    I know for few possible approaches:

    1. Create separate column for each of three parents in Timesheet table (AssistanceTaskID, WorkTaskID, AbsenceID), and then assure with constraint that exactly one of three keys exists for each row. I don't know possible disadvantages, except lot of NULL values in that table (which will be quite large after some time), and that database integrity will not be so "strong".

    2. Create three Timesheet tables, one per each type of task. This will probably mean "stronger" database integrity, but I suppose that I will need to create UNION queries for almost everything related to Timesheet tables.

    3. Leave only one key (TaskID) in Timesheet table, and left multiple parents (as it is shown on the attached picture). What are advantages/disadvantages in this approach ?

  • February 11, 2009 at 5:06 am

    #942010

    Forgot to attach diagram...

  • February 12, 2009 at 3:55 am

    #942690

    No replies ?

  • February 12, 2009 at 2:01 pm

    #943179

    [font="Verdana"]I would use the first form, because then your declaritive referential integrity will work.

    If you are particularly worried about NULLs in key fields, you can create mapping tables if you wish.

    A -> B -> C

    D -> E -> C

    F -> G -> C

    Me, I'd just live with the nulls.

    [/font]

  • February 17, 2009 at 1:28 pm

    #945442

    Perhaps you could roll your 3 types of task into one table, then your timesheet records have only one parent. Create views that separate the 3 types of tasks into distinct sets.

    if that doesn't work for you, you could go with your idea #2, and you could hide the UNIONs by creating a view.

  • February 18, 2009 at 3:04 am

    #945755

    if that doesn't work for you, you could go with your idea #2, and you could hide the UNIONs by creating a view.

    Thanks for replies.

    What about performance issues when using views. As far as I understand they are not precompiled as sprocs ?

    Since columns for each type of task is somewhat different, I don't think I would join it in one table. I meant in (1.) to create one "supertype" table, consisting only of primary key (TaskID) and discriminator, and to join it to 3 "subtype" tables for each task type 1:1.

  • February 18, 2009 at 11:59 am

    #946192

    [font="Verdana"]You know, there was a time when the overhead required to compile/translate SQL was significant. Unless you are running your database on an ancient server, I think you should just focus on I/O as the determining performance characteristic in your designs. :D[/font]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply