I have a simple patients table and lots of Lab tests table. Every patient can have many Lab tests. What i want to do is to only query the tests which are already entered for that specific patient and avoid joining all 100000 tables(which will take lots of resources to execute).
Should i create a table and save which tests are conducted for the patient for later use?
PS: i have to mention that currently im using MYSQL and Doctrine 2(which is an ORM)and I'm out of options for using non-mysql solutions and i know that there are many Electronic medical records softwares using SQL but they are not opensource so we cant study them.