Children with more than one parent

[balachandar]

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-Modules-Bugs)

Thanks and Regards
Balachandar

 

[johnherman]

You no longer have a child-parent relationship. YOu now have a many-to-many relationship and need a bridge (aka associative or relation) table to join them.

ProductTable
Product ID
Product Code
Product Desc
Product Pkg
etc.

BugTable
Bug ID
Bug Category
Bug Desc
etc.

ProductBugTable
ProductID
BugID

In this case ProductBugTable is the relation, associative, or bridge table allowing you to map multiple bugs to multiple products.


-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[johnherman]

Oops. Forgot to add module.

ModuleTable
Module ID
Module Code
Module Desc
etc

ProductModuleTable
Product ID
Module ID

You can track the bugs at Module level or Product level.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[balachandar]

However , In the above schema, I will be able to add a module with out belonging to product. That should not happen. Parent child relationship still holds good. AS you can not have a module with out a parent. However there are modules which belong to more than one prouduct. I want to the track the bug both product and module level.

 

[ceh4702]

One way I have heard of this being done is where work is documented as projects and the project has a billing for the hours but that is a completely differrent technique that is more applicable to how some engineers or designers work on multiple projects and submit a time sheet for each project.

If you do not like my post feel free to point out your opinion or my errors.

 

[SQLSister]

You don't say what database you are using, but you can enforce the relationship through a trigger if your database has that capability. If it doesn;t your user interface will have to manully check if the record exists in the table before performing the insert.

Questions about posting. See faq183-874

 

[RiverGuy]

I think its pretty much impossible. You want the same exact module to be a child of many possible products. This means you need a separate Module table--or else you will compromise your data integrity. Therefore, the only way to join a Module to many Products is to have a Module-Product table. So, you will have to allow a Module to be created without a Product as a parent--they're in different tables.

The way I would try to get around this is in your application logic--as soon as the user creates a Product--prompt him or her to PICK a module (which will be placed in the Product-Module table) or ADD a new module.

 

[johnherman]

SQLSister has the right idea. The restriction of needing at least one Product for a Module cannot be enforced by the design. It must be enforced programmatically, either via trigger or via the data entry application.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[RiverGuy]

I'm interested in how the trigger would work. I can't seem to understand it. The way I see it is that the Module has to be created before its link to its parent can be established. Therefore, triggering the Module table wouldn't work, because the database would not know which parent to link it with.

I agree that a trigger would definately be easier and probabably more robust, but I'd like to know how it would work, for my own benefit.

 

[SQLSister]

A trigger inthis case is really ony a way to preserve data integrity from people who are not using the user interface. It could be an instead of trigger. If the insert has no productID or has one which does nto exist inteh product table, then the insert is not performed.

However, I would still handle this at the user interface as well, no point in sending an insert across the network which must fail. I would have a drop down box with all the products which is limited to the list and have the interface provide an error message if no value is selected. Or a subform where multiple products can be selected, but again an error message if the user tries to savve the record without selecting any. I would also have a button avaliable on the form to add new products.



Questions about posting. See faq183-874

 

[ESquared]

Is there any use in turning the relationship upside down and thinking of the module as the parent and the product as the child?

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)