Join 34,000+ subscribers and receive articles from our
blog about software quality, testing, QA and security.

'tests' vs 'test_changes' tables


I am trying to understand the schema and when entries are made in certain tables. Two tables in particular are interesting - tests and test_changes. It seems that and test_changes.test_id hold similar data - the test ID of the execution. Yet, I am not able to reconcile the numbers.

Here are the queries I am using:

select count(*) from tests;
# 2425683

select count(*) from test_changes;
# 21298

select count(*) from tests inner join test_changes on = test_changes.test_id;
# 21298
# so all test IDs from test_changes are present in tests

select count(*) from tests where id not in (select test_id from test_changes);
# 2410918

I understand test_changes has the entries for comments as well but the rows for common test_id values is confusing me. How are these 2 tables related to each other? When does an entry make its way into either or both tables? When an entry is deleted from one, is it also deleted from the other?

Appreciate any help.


After looking and testing a little bit in my company’s test environment - the Tests table holds the data for each Case for a specific Test Run. Nothing gets added to the table until a Test Run is created for a Project. So if you Create a second Test Run (R2) there will be a record in the Tests table for each Run )R1 and R2). At this time - (nothing has been assigned) there are no records in the Test_Changes because there are no changes to a case in a run.

Any changes to the Case in a Test Run is tracked in the Test_Changes table. From status changes, comments in the results area, assigning the case to anyone other than the original assignment is tracked in the Test_Changes table.

You numbers suggest that you have a lot of cases in Test Run(s) but few have had any changes to the cases after the initial creation of the Run.

TR does not allow deleting the records in the Test_Changes table for Auditing reasons.

The Tests table is the parent to the Test_Changes table. Test _Changes table should have the Test_Ids from the Tests table.