Database Design / Schema For A Vet CLinic's Laboratory.

So I am currently working a small project for a Vet Clinic in my local community. The project in a nutshell simply involves transforming their operations into a digital experience from the good old paperwork. I decided to split the project into phases Now, I am at a phase where I need to create an interface where the Vets can fill out a form in order to record the results of a lab test.

I have been giving this a thought for the past 24 hours but the solutions I come up with I am certain aren't the best at all.

so the issue is that the Vet Clinic has a lab department that carries out or performs quite a number of types of lab tests.

Right now. From the data, I am working with, there are categories of Lab Test, Under these categories, you have different types of Lab Test (A category can have multiple types of test).

The issue is that every type of test is unique and comes with its own format for the way the reports are generated.

Any suggestions on how I can go about this? both Frontend and backend? (On the frontend I know I am going to have to find a way to present different forms for each test result type).

fylzero

6 years ago

Level 67

This data structure should help you make your associations.

Models: Categories (has many Tests) Tests (belongs to many Categories, has one Format) Formats (belongs to many Tests)

Tables: labtest_categories, labtests, report_formats

labtest_categories

id | name

labtests

id | category_id | format_id | content

report_formats

id | content

Tackle writing the CRUD first, set your relationships, once you build the basic crud frontend, then figure out how to go about the front end.

I would think you'll have more entropy based on how the labtests are related... like are they bound to clients, veterinarians? That's another level you'll want to consider.

Once you plan how to scope your data... just scope your front end however you need...

Labtests::where('category_id', 2);

Hope this helps.

24 likes

deladels

6 years ago

Level 4

Thank You @fylzero. I will definitely wrap my head around your suggestion and come back with feedback.

jlrdw

6 years ago

Level 75

A little more to it, there's A/R, A/P reports, etc, also have a look at:

https://laracasts.com/discuss/channels/general-discussion/rental-management-db-schema

Edit: If you do not already have one, get yourself a good query designer.

And this is a good site: http://www.mysqltutorial.org/

deladels

6 years ago

Level 4

@fylzero I wrapped my head around your suggestion. But the issue here is that because every lab test is unique, the report content will be unique as well. So how exactly would you go about storing that content? What data type are you going to use? probably JSON?

@jlrdw ALright Sure. I will. Thanks for the extra tip.

Please or to participate in this conversation.