View for real-time list pulls in an OLTP

Question

View for real-time list pulls in an OLTP

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

vilyana (9/19/2016)
Thank you for the concern.
The ERD and the View columns in the image are exactly what they need to be to illustrate the starting point and desired result (and this is all they are intended to be). Every other piece of info is removed to simplify as much as possible and focus on the actual question which @frederico_fonseca provided a solution for (clearly faster and better than mine - I used the pattern to write the full solution on the real tables) . And yes, 0 to N Treatments, 0 to N Therapies for each treatment, 0 to N Med Courses for each treatment are possible (as shown in the erd), but only the first 2 of each based on ID (not date) are required in this particular view.
The only problem with the ERD is that the column and table names are not obscured with a generic naming convention so there won't be anyone picturing over-medicated (or worse) patients without knowing anything about the context, purpose or the actual system. I wonder if I can still finish the last semester of grad school and work full time from jail? Ugh, it would be really unfortunate getting locked up for limiting the info on a sample diagram and column names in a post 🙂 I promise to use Tbl1, Col1,.., ColN next time I ask for help with improving my SQL code.
I've marked the working solution to my question. Now I’ll be going back to assisting people who need care.
Kind regards,
Vilyana

Vilyana,

"first 2 of each based on ID" is not any better than "first 2 of each based on date".

It's actually worth.

Dates may actually have some relevance to real world", when ID's are totally made-up numbers.

Relying in sequential order of ID's is the worst choice you could make.

Imagine a simple situation.

Doctor defines for a patient a treatment course 1.

It gets ID=1.

Then doctor prescribes medications for that course.

It also gets ID=1, in another table.

Then doctor defines a course 2: ID=2, with corresponding medication course, which also gets ID=2.

So far so good.

But minutes later the doctor becomes aware that for some reason the medication course #1 won't work - some medication is not available at the moment, or patient informed about some allergies, etc.

So, doctor deletes the initial medication course and prescribes another one instead of it.

ID's 1 and 2 are already used, so the medication course for treatment course 1 gets the next available ID=3.

But what your view will show, based on sequence of ID's?

It will allocate medications for course 2 (ID=2) to treatment course 1 (ID=1), as they both have "first" from currently valid ID's, and medication course 1 will be allocated to treatment course 2, as their IS's are both "second".

What could be consequences?

I would not suggest you to try it out.

Relaxing medications on intensive therapy course will most likely cause a heart attack.

And then you quite possibly start exploring your options to sit graduation exams in jail.

Better fix the database design.

These things better to learn in theory than to experience in practice.

_____________
Code for TallyGenerator

Grumpy DBA SSChampion Points: 12515 More actions · Answer 2

Please let me know which health system/hospital/physician office this will be used so I can avoid going there.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

vilyana (9/19/2016)
Thank you for the concern.
The ERD and the View columns in the image are exactly what they need to be to illustrate the starting point and desired result (and this is all they are intended to be). Every other piece of info is removed to simplify as much as possible and focus on the actual question which @frederico_fonseca provided a solution for (clearly faster and better than mine - I used the pattern to write the full solution on the real tables) . And yes, 0 to N Treatments, 0 to N Therapies for each treatment, 0 to N Med Courses for each treatment are possible (as shown in the erd), but only the first 2 of each based on ID (not date) are required in this particular view.
The only problem with the ERD is that the column and table names are not obscured with a generic naming convention so there won't be anyone picturing over-medicated (or worse) patients without knowing anything about the context, purpose or the actual system. I wonder if I can still finish the last semester of grad school and work full time from jail? Ugh, it would be really unfortunate getting locked up for limiting the info on a sample diagram and column names in a post 🙂 I promise to use Tbl1, Col1,.., ColN next time I ask for help with improving my SQL code.
I've marked the working solution to my question. Now I’ll be going back to assisting people who need care.
Kind regards,
Vilyana

It's not any of that that we're concerned with, Vilyana. It's the fact that the data between the therapy and the meds is going to be arbitrarily joined by date or the order of an Identity column instead of DRI (non-null Foreign Keys, in this case). Such an arbitrary join doesn't guarantee that the correct meds will go with the correct therapy and could even allow for the duplicate administration of the meds or for meds to be given in the wrong order or even not at all. That could kill someone and it needs to be fixed not ASAP but right now.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)