Children with more than one parent

Balachandar Gan · #1 (**permalink**) 10-26-04, 13:38

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

rajiravi · #2 (**permalink**) 10-26-04, 15:44

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

Internet Services

Web Development

Digital Marketing

Consumer Tech