My company uses Ignition as a form application to input all data from hourly quality checks for Production, QA, and Warehouse. Each specific test has its own table. Database is separated by plant.
I got a request from a plant to create a report with all Out of Specs in a shifts worth of data from all the tests we perform and am having a hard time accomplishing this without creating a query for each specific test that we perform since each tests data is stored in a different database table (about 20+ tests, so 20+ tables and 20 different queries). There is an OutOfSpec table in each table, but the columns in each table are different depending on what checks we have for the test. (Screenshots of a few example tables can be found below).
Is there a better way to do this rather than creating a query for every test I want to include in the report?
However, it would be considered a best practice to factor out the common parts of those tables into a test_master table, with a auto-generated primary key. Then the test-specific tables would only contain a reference (aka foreign key) to test_master besides its other columns. the test_master table would typically include the overall good/no good result for the test, so the specific tables can be omitted from high-level queries.
Thank you for the suggestion. I was thinking the same thing. Have an "OOS" table with columns: TableName, TestID (ID of the OOS entry in its table), its own primary key ID, and maybe the date in order to easily query/identify the OOS entries from all the tables.
While on the topic, hypothetically, how would you structure these tables in the future to make working with the type of data I specified above a bit easier?
With your current non-relational database structure, you can create a view in the SQL Server that UNIONS the tables similar columns and does a WHERE OutOfSpec=1
Then you can query against that view for your report.
If they need it more detailed you will end up either writing some custom sql functions or as you said, 20 queries..
A client I helped with a similar situation uses a single tall table for measurements in a hierarchy like so (approximately, application details omitted):
a) Metadata (static)
Test names and type codes
Sample types by test type, with sample type codes
Measurement types and methods, with method codes
Good/No good criteria by test type, sample type, and measurement type
b) History
Test_Master (unique ID, timestamps, test type code, judgement roll-up)
Sample_Master (unique ID, reference to test master, timestamp of sample acquisition)
Measurements (sample unique ID, measurement type, measured value, judgement result for the one measurement)
