seeking ideas for table structure

[Kyosa]

VERY sorry for the length:

basically i am trying to create a database of Medicare rules called LMRP's regarding payment coverages for certain procedures etc. So first, the info in the database is subject to frequent changes hence my cautious approach. just as a bit of background, Medicare issues guidelines for certain procedures that state under what circumstances medicare will pay for them. These LMRP's can get quite detailed but most importantly for my purposes they list what procedure codes they pertain to as well as what diagnosis codes are allowed with those procedure codes. What i am trying to do is have a .asp page that a person can type a procedure code and a list of valid medical diagnosis will display for that code as well as a bit of other info re: the LMRP.

the part that's throwing me is how best to relate the procedure and diagnosis codes to the rest of the info. There are several thousand diagnosis codes and only a portion of them is allowed in each rule. Likewise for the procedure codes. So i might have a rule for chest-xrays that covers, for example, 4 procedure codes (71010,71015,71020,71022) and that states say 600 diagnosis codes are considered valid. I

Initially i thought about having one table for the basic rule info (name of LMRP rule, effective date, expiration date, note field for basic coverage info, etc), another table for the procedure codes (several thousand of them) with perhaps a field stating which LMRP if any they belong to. (typically each procedure code is only assoc to one LMRP), and then finally a third table w/ the diagnosis codes also with some indicator as to which LMRP they also belong to. Problem again is that a diagnosis code could be associated with none of the LMRPs or perhaps All of them.

i guess it's just a flexibility issue as well. new rules are added all the time and the old ones are constantly getting updated. so i need the structure to be equally as flexible.

any ideas would be GREATLY appreciated.



Kyosa


"The key to flexibility is indecision!"

 

[GoDawgs]

I think the structure seems like it should be something like this:

LMRP table
Procedure Code table
Diagnosis Code table

Then you're going to need an intermediate table between Diagnosis Code and LMRP (since it's many to many, meaning 1 Diagnosis Code could be associated with many rules and 1 rule could be associated with many Diagnosis Codes). That table would really just contain the Primary Keys from each of the two tables and maybe it's own autonumber or something if needed.

You may also need to do this with the Procedure Code table (create an intermediate table I mean). You said it's only associated with one rule...and if that's definitely the case then you can store the LMRP code in the Procedure Code table, but the "typically" is never a good sign...and the first time that one Procedure code is related to many rules you're in trouble and have to change it all and create the intermediate table...so I'd suggest just doing that now.

Basically the table setup won't be too hard for your db, the problem is going to come once you start having to enter the data...you'll need some forms set up to ease you or the data enterers through this. Good luck...hope this helps.

Kevin

 

[Kyosa]

Kevin,

thank you for the advice. I will give that a try and see how it goes for me. thanks again.


Kyosa


"The key to flexibility is indecision!"

 

[willir]

Kyosa

I tried to post to this the other day, but had to attend to some family issues part way through, and lost the posting before it was completed.

GoDawg has given you a great start. But I want to dig just a bit further.

I appreciated the information you have provided -- you have put a lot thought into it, and you know what you specifcally want. But I was confused about one aspect. From my perspective, the clarification on this issue would impact the design of your database.

As I understand it, the LMRP provide "rules" that dictate what procedures for specific diagnosis are covered under the medicare plan.

The user enters a procedure, and your database will display relavent diagnostic codes and some LMRP information.

Basically, if I understand you correctly, my confusion resides in that the LMRP data flow goes from LMRP -> Procedure -> Diagnosis. And from your descruiption, you seem to want to go from Procedure -> Diagnosis -> LMRP.

Yes this is possible, but it generates some uncertainty on the specific relationships. For example, the data flow could be Diagnosis <- Procedure -> LMRP.


So humour me...

Can a procedure have more than one diagnostic codes? And does this affect the LMRP?

Can a diagnostic code have more than one procedure? And does this affect the LMRP?

As per GoDawg, we know you have a many-to-many relationship between diagnostic code and precedure, but how does this impact the LMRP? Is it a simply arrangement, or is complicated?

The outcomes from this train of thought, as I understand it, if it is more than a many-to-many (M:M) relationship between diagnotic codes and procedures is that you may have two possibilities...

A M:M relationship for LMRP to Procedures
LMRP code + Procedure Code
and
a M:M relationship for Procedures and Diagnostic codes.
Procedure Code + Diagnostic Code
Where you may have to use two intermediate or join tables.


Or, you may need what I call a &quot;profile&quot; table

LMRP code + Procedure Code + Diagnostic code


I probably just confused the heck out of you. Sorry.


But what I would do next is create some sample data, and see which model fits best. Try to stick to the rules of normalization. i.e. avoid redundancy and such, and it should become fairly easy to determine which works best.

Richard

 

[Kyosa]

Richard,

thank you very much for the response. I really appreciate it, esp having to come back to it like that..I must say this is proving a bit more complicated than i first anticipated. Of course having not designed an Access database in about 3-4 years, it's proving tedious..basically like learning from scratch.

I was intrigued by the data flow analysis you mentioned and i think that may be part of my problem.

let me try and clarify a bit more using some data from an actual LMRP rule.

LMRP for diagnostic Mammograms covers procedure codes 76090,76091,g0204,g0206,g0236. Now for these 5 codes it states which diagnosis codes will be considered valid (there are many, but for examp..1725,1735,174,1741,1742). The rule also lays out some other conditions under which the procedures would be covered. That's pretty much it.

So it was looking to me like a table for base lmrp info (name,eff dat,end date, and a memo field to enter any other conditions.) then separate lookup tables for diagnosis and procedure codes.

each LMRP is for a set of related procedures codes..could be 1 code or could be 15..but a procedure code could never belong to more than one LMRP. The diagnosis codes on the other hand belong to multiple LMRP rules.

When you mentioned the flow of data it got thinking again. The thing is that the people using this will not know what the LMRP name is or even in most cases if a particular procedure code is covered by one. That's was why i was wanting them to be able to type a procedure code and then have any relavant info display.

Anyway, The design that GoDawgs suggested appears work but i'm getting some anomilies in my queries..but pretty sure some of that is due to me trying to &quot;relearn&quot; this.

i was curious if you might expand on your profile table idea. that sounded interesting.

anyway, thanks again




Kyosa


&quot;The key to flexibility is indecision!&quot;

 

[willir]

Kyosa

Since most relational database do not automatically handle many-to-many (M:M) relationships, an intermediary or join table is established. A one-to-many (1:M) relationships is established to the foreign key on the join table.

Using your data, but I will ad lib a bit.

tblProcCodes
- procedure codes + explanation + other properties for Procedure codes
- primary key, either autonumber or procedure code itself
76090 - Regular mammogram
76091 - Needle biopsy
g0204 - Local annesthetic
g0206 -

tblLMRP
- LNRP codes + other properties for LMRP
- Primary key - autonumber
1 Mammogram
2 Needle Biopsy

tblDiag - Diagnostic codes + other properties for diagnosis
- primary key - diagnostic code or autonumber
1725 - Post 50 routine breast exam
1735 - Found lump
174 - Comapny beneift, better health plan
1741 - Suspicious xray, high calcification nodes


A typical Join or Intermediary table would be...
tblProcDiag
- Primary key - Procedure code + Diagnostic code
Proc Diag
76090 1725
76090 1735
76090 174
76091 1735

The LMRP &quot;profile&quot; table would be a variation on this.
- Primary key - LMRP code + Procedure code + Diagnostic code
LMRP Proc Diag
1 76090 1725 - Mammogram, routine over 50
1 76090 1735 - Mammogram, found lump
2 76091 1735 - Needle biopsy, found lump
2 76091 1741 - Needle biopsy, suspicous xray
Note:
- No entry for Diagnostic code 174 since this is paid by a comapny as part of a benefit plan)
- LMRP codes and procedure codes seem redundant but they are not since you can have a procedure with no LMRP code

The advantage of using this &quot;profile&quot; is that you can search for LMRP, procedure or diagnostic codes and retrieve the other two components. It also allows simplifies editing -- add and drop procedures and diagnostic codes.

You can also add other bits of information to the profile table. For example a date field and a comment field. You receive an ammendment for an LMRP. You can add the profile, date stamp it when the addition was made, and reference the ammendment in the comment field. Now you have documentation on when this code was added and why.

The disadvantage is that the table can get pretty large; larger than a M:M table since one LMRP will have several Procedure x Diagnostic codes.


Which has made me think a bit more.

Another way to do this is to forget about the relationship between the diagnostic and procedure code, and just focus on the LMRP code. (I probably have some people ready to thrash me for this statement)

From your data, there seems to be a M:M relationship between LMRP and Procedure codes. And a M:M relationship between LMRP and Diagnostic codes. From the perspective of the LMRP, the relationship between diagnostic codes and procedures may not be important.

Using the same data...

tblLMRPProc
LMRP Proc
1 76090 - Mammogram
2 76091 - Needle biopsy

tblLMRPDiag
LMRP Diag
1 1725 - routine over 50 exam
1 1735 - found lump
2 1735 - found lump
2 1741 - suspicous xray

This approach would require less data entry, but you will have lost the relationship between Procedures and Diagnosis. And yoy would not have the ability to control to document ammendments.

Will it work??
The user enters the Procedures code.
Your system then needs to find the LMRP code or codes
Then you need to run a query for each LMRP code and retrieve the valid Diagnostic codes. So yes, it seems it may work, but a little more tedious then the profile solution.


So now you have more food for thought.

Richard

 

[Kyosa]

Richard,
Thanks again. And, your are correct..most def. more food for thought!

I will experiment with these options and see how it goes.

again, i appreciate you taking the time.




Kyosa


&quot;The key to flexibility is indecision!&quot;