Difficulty to form Relations and Normalize

[shouvik.d]

Hi All,

We have many test artifacts and each unit is known as Unit Under Test (UUT). Each UUT is unique entity so it can be considered as a Primary Key.

Also there are various defect categories (viz. CAT-I,CAT-II). Moreover each Category can be broken down into exact defect name. so CAT-I would have a table of its own having the IDs for each of the exact defect. Similarly for CAT-II

Now each UUT can have multiple defects associated with it. e.g. UUT1 has a defect in CAT-I and two defects from CAT-II.

Now even if I have a foreign key I am not able to associate multiple defects to one UUT in form of a table. Can anyone suggest a good schema so that I can be able to associate a UUT to multiple defects.

I want to have the facility of bi-directional search like...I should be able to search what are the defects and what category in a UUT. Conversely I should also be able to find a defect exists in which UUTs.

Kindly guide me on this aspect. I had thought of a junction table with a many-to-many relationship but still I am not able to associate a single UUT to multiple Categories and the defect names inside each category.

I hope I could explain things properly.

[tesuji]

hi,
you may specify your tables and key firstly, so we get able to check it and hopefully spy out some mistakes.

brs,
cliff

[shouvik.d]

The table design and relationship is not frozen as of till now. I thought it would be better to actually form a schema first and then go ahead designing the tables, keys and the respective relationships.

For this only I need some help.