Tables and Relationship suggestions (1 Viewer)

[silversun]

Hello all,
I've created a small, simple DB to store/track my daily task(s).
Each project can have one or more complaints. I will have to run some general tests for most of the cases before I run some specific tests according to each complaint(s) to confirm if the customer was right about their complaint or not.
Each complaint has a set of tests to run in the lab and most of the tests are common in most of the complaints. Therefore I have to perform general tests in the lab on each device before specific tests and store all the tests and complaint(s) per project in a table. Also in that table I will need to store the status of each test for future use. I will update this status every time I finish a test, (default value can be "Not done yet" for example).
After I finish all the tests I will update the taskCompleted column in t_projects.

Now I need you to help me relate the tables properly and create more tables where I need.

Please check the attached picture and DB and let me know if you need me to explain my question or DB more.

 

[jdraw]

Perhaps something along this set up

 

[Pat Hartman]

Your relationships are incomplete and you're missing at least one table.

You need one relationship that defines the tests that you have to run for each complaint and the new table is to show that the test was run and what the results were.

 

[Pat Hartman]

As you can see, @jdraw and I came up with similar concepts but different solutions. That's because only you know your actual situation

 

[XPS35]

This would be my suggestion

I don not see the need to have a many-to-many relationship between project and complaint. Each complaint belongs to just one project.
In t_test you find general descriptions of all possible tests. t_test_comp tells you which tests are run for a complaint.

I don't think taskCompleted should be in t_project. You never can tell. There always can occur new complaints. It could be a item in t_complaint but you do not really need it. If all tests are done the complaint is completed (?).

 

[mike60smart]

I agree with xps35 but I would also add a Test Date field to t_test_comp table

 

[Pat Hartman]

You need to separate the TWO paths. One path lists the test options for a complaint but the other lists the tests that were performed for THIS SPECIFIC complaint and records the results.

 

[mike60smart]

My suggestion is:-

 

[Pat Hartman]

What we don't know is - are there specific tests that are always performed for a specific complaint? That is what my model assumes based on the description.

So, if the complaint is x, then run tests 1, 22, and 45 but if the complaint is y, then run tests 44 and 45. Then you need a table in which to record the results.