Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2002
    Posts
    3

    Question similar Tables or a more generic table

    Hi,

    I'm creating a project of my own and I have a set of tables that represent entities that may share another common table.

    For example:

    Let's say I have (4 tables, but I show only GOAL and PROJECT):

    GOAL
    ---------
    Goal_ID (PK)
    ....


    PROJECT
    --------
    Project_ID (PK)
    ...

    I'd like to have a set of Requirements for Goals and Projects.

    So I create:
    REQUIREMENT
    -----------------
    Req_Id (PK)
    Related_Class_ID (instance would be a Goal_ID or a Project_ID
    Related_Class (instance would be 'PROJECT' or 'GOAL')
    Description
    ....

    With this I can have a table that can save requirements for Goals and
    for Projects (or Activities).

    My question is as follows: What if there are other characteristics that are
    shared by a Project or a Goal like let's say: Benefits.

    Should I create another table like REQUIREMENT, like in:

    BENEFITS
    -----------------
    Benefit_Id (PK)
    Related_Class_ID (instance would be a Goal_ID or a Project_ID
    Related_Class (instance would be 'PROJECT' or 'GOAL')
    Description

    or instead I should create a more generic table that can include REQUIREMENTS and BENEFITS like in:

    CLASS_DETAIL
    -------------------
    Detail_ID
    Related_Class_ID (instance of Goal_ID or Project_ID)
    Related_Class (instance would be 'PROJECT' or 'GOAL'
    Category_Type (instance would be 'REQ' or 'BEN' from CATEGORY table)
    Description

    I can do both but could your give me please some hints as to what implementation would be better?

    Thanks a lot for your help!

    Gorchis

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: similar Tables or a more generic table

    It's difficult to give a definite answer without knowing your requirements in more detail. Do REQUIREMENTS and BENEFITS really have the same structure, i.e. just a Description, or do they actually each have a set of attributes of their own? If they have the same simple structure, then combining them as you have suggested may be OK; if not, then it would be better to keep them separate.

    Your design for the Related_Class_ID and Related_Class columns means that you cannot define foreign key constraints to ensure referential integrity - you would have to use triggers. Some alternatives you could consider:

    1. Define a foreign key column for each potential master:

    REQUIREMENT
    -----------------
    Req_Id (PK)
    Goal_id (FK)
    Project_ID (FK)
    Description
    ....

    This requires a check constraint e.g. CHECK( (goal_id IS NOT NULL and project_id IS NULL) OR (goal_id IS NULL and project_id IS NOT NULL) )

    It actually simplifies your SQL, because you can join master to detail with one condition (req.goal_id = goal.goal_id) rather than two (req.related_class_id = goal.goal_id AND req.related_class = 'GOAL').

    2. If PROJECT and GOAL are quite similar entities, you may prefer to design them as subtypes of a more generic entity. I'll call it THING, but you should be able to come up with a more meaningful name (if you cannot, then perhaps this approach is not right):

    THING
    ------
    thing_id (PK)
    thing_type /* 'PROJECT' or 'GOAL' */
    ... /* Attributes common to PROJECT and GOAL */

    PROJECT
    ----------
    thing_id (PK, and FK to THING)
    ... /* Attributes specific to PROJECT */

    GOAL
    ----------
    thing_id (PK, and FK to THING)
    ... /* Attributes specific to GOAL */

    REQUIREMENT
    ----------------
    req_id (PK)
    thing_id (FK to THING)
    Description
    ,,,

    With this approach, it is a good idea to create views that pre-join the subtypes to their supertype, for the convenience of users and applications, e.g.
    CREATE VIEW v_project AS
    SELECT thing.thing_id, thing.[other_columns]
    [project.columns]
    FROM thing, project
    WHERE project.thing_id = thing.thing_id;

    These view will look exactly like the original tables before you introduced the supertype. Some database (e.g. Oracle) allow inserts, updates and deletes on such views e.g. via triggers.

  3. #3
    Join Date
    Oct 2002
    Posts
    3

    Re: similar Tables or a more generic table

    Thanks a lot Andrew,

    I was thinking that REQUIREMENTS and BENEFITS should
    definitely have different attributes in order to require
    different tables. So thanks for your suggestions and I think
    that's the way to go. I appreciate your valuable insights
    as to how to design what I'm doing.

    Regarding the THING Table For GOAL and PROJECT, I was thinking if
    this should still be applied if there is a 0-M relationship
    between the GOAL and PROJECT tables themselves. I think I still can
    altought in fact, I have a total of 4 tables structured to represent a Tree
    as follows:

    1 MISSION can have 0-M GOALS; 1 GOAL can have 0-M PROJECTS;
    1 PROJECT can have 0-M ACTIVITIES;

    For this scenario I was thinking on creating a table or view with
    a cross-reference for all ID's in case I want to get faster to all
    ACTIVITIES involved on a certain MISSION.

    MISSION_ACTIVITIES
    -----------------------------
    Mision_ID
    Goal_ID
    Project_ID
    Activity_ID
    /* Attributes related to the Tree structure */

    If you may have any other insights with regard to this scenario,
    please let me know. I'd really appreciate it.

    Thanks in advance,

    Regards,
    Igor

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: similar Tables or a more generic table

    Igor,

    I think the supertype could still be used here - I still call it THING but I'm sure a better name could be found!

    I have sketched a simple ERD to illustrate - hope it loads up OK.

    As for the MISSION_ACTIVITIES table or view: I would definitely prefer to go for the view - it will look after itself, whereas a table would have to be maintained and kept in sync with the base tables (e.g. via triggers). The view is quite simple, so I would not expect performance to be a problem.
    Attached Files Attached Files

  5. #5
    Join Date
    Oct 2002
    Posts
    3
    Hi Tony,

    I could open the file but saw nothing only garbage. Could you please send it over again?

    Thanks again,

    Igor
    Last edited by Gorchis; 10-28-02 at 17:13.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •