Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2004
    Posts
    1

    Post Children with more than one parent

    Hi All,
    I am trying to design the database for a bug tracker application.
    This is the information I am trying to model.

    There are n number of products in the project. Each product has one or more modules. There are some modules that are common to more than one product. (i.e) Each module(child) can have more than one parent(Product).

    Every bug reported can have more than one product impacted(Therefore modules as well).

    These are some of the questions that I would need to answer.
    1. bugs details product wise.(Only For product A, Only for Product B and bugs that Impacted both Product A and Product B)
    2. Modules that are common to products.

    Also I need to ensure that No module can be created with out a parent associated with it.
    How I can complent this with a E-R diagram or What could be best tables to implement this relationship.(Product-Module-Bug)

    Thanks and Regards
    Balachandar


  2. #2
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    A simple design with the following tables should suffice:

    Code:
     Table product (id, name, description, ...)
    module(id, name, description, ...)
     
    product_module_usage(product_id references product(id), 
    	 module_id, references module(id) ...)
    The primary keys are in bold.

    This way, a module can have as many parents as it cares to.

    For the bug tracking part, one way of doing it is:
    Code:
     Table bug (id, name, description, reported_date, resolved_date, ...)
     
    Table affected_modules(bug_id, references bug(id), 
    module_id references module(id),....)
    Columns involved in the primary key may not be null, thus satisfying your business requirement.

    You can create a view called affected_products by using the affected_modules table and the product_module_usage table.

    Conversely, you can create the table affeted_products and the view affected_modules. Either way, what is a table and what is a view does not matter much.

    Hope that helps.

    Ravi

Posting Permissions

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