Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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.

share|improve this question
Why do you have many tables for lab tests? Do they all have the same columns? – Nick Chammas Jan 9 '12 at 20:15
Hi @Nick Chammas .Its because tests are different subjects.eg: Blood tests, Urine tests, Thyroid tests,etc.Haven't i violated Normalizations If i put all the fields in just one table? I will thank you if you enlighten me. – afterburner Jan 9 '12 at 20:35
1  
If the tables all have the same fields, you could easily combine them into one table with an additional column that indicates the type of the test. If the tables all have radically different columns, then you may need to keep them separate as you have done. Which is the case? And how many tables do you currently have? – Nick Chammas Jan 9 '12 at 20:41
@Joel Brow nsorry for the delay.Diffrenet tables include diffrenet tests eg:patient_test_blood contains Hemoglobin,White blood cells and Platlets,patient_test_urine contains urine color,urine volume etc.I am still in design process and i have not created any tabled yet but im aware that there are thousands of tables around.I could put all tests in only one table but its anti normalization. – afterburner Jan 12 '12 at 15:44
1  
drfanai, I believe you meant to add this comment on Joel's answer, not here. He won't be alerted to it until it's posted on his answer. Also, it is perfectly fine to have all your tests in the same table if they share similar properties and this would be the correct, normalized way to do it. In your particular case, though, you may have to look at some of the other options Joel proposed. – Nick Chammas Jan 12 '12 at 16:03
show 1 more comment

1 Answer

up vote 1 down vote accepted

As Nick Chammas pointed out in his comment, your test tables should be combined if they have the same fields and segregated if they have very different fields.

It is difficult to imagine 100,000 different test tables. If this is not complete hyperbole then you might need to consider other options.

One option would be a document-oriented database. This would be efficient for storage of loosely structured data and easy retrieval of data for a particular patient. It would be pretty useless for rapid retrieval of data for similar tests across multiple patients.

Another option would be Entity-Attribute-Value (EAV). EAV is considered an anti-pattern by most people, but yours may be a situation where it could be useful. EAV would be less efficient at retrieving the results of a particular test for a particular patient, but it would certainly be better at getting all results for a particular patient than joining thousands of different test tables. It would also be better than other approaches for getting the same test result values across multiple patients.

share|improve this answer
Brow sorry for the delay.Diffrenet tables include diffrenet tests eg:patient_test_blood contains Hemoglobin,White blood cells and Platlets,patient_test_urine contains urine color,urine volume etc.I am still in design process and i have not created any tabled yet but im aware that there are thousands of tables around.I could put all tests in only one table but its anti normalization. – afterburner Jan 12 '12 at 16:50
2  
Normalization is a tool (rule of thumb) for keeping you out of the most common kinds of trouble that people get into by bad database design. If you need 1,000s of tables for that many different tests then I suggest a document database if results are only retrieved one patient at a time or EAV is you need to retrieve similar test results across multiple patients in the same query. – Joel Brown Jan 12 '12 at 21:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.