This article was initially available on our podcast. Click here to listen.

Most of us in medical billing or revenue cycle management don’t think of the concept of a relational database or the difference between a flat file and a relational database.

Discuss flat files

First, what is a relational database versus a flat file? A relational database means you have separate tables. So you have a table that lists all of your patients, for example, and then a different table that lists the patient encounters or visits. For each one of those patients that you have in the other table (patient one, two, three, four), there is a series of visits that link to those via a key. Still, they’re in a separate table like a separate report, basically, within the database, and they’re tied back to each other. You can have many, many different tables linking in different ways and hierarchies and all those kinds of things, but the point is that they’re all separate. You’d have a table for patient visits, patient denials, a table for patient insurances and demographics, etc. They all link to each other in this relational database, relational meaning they relate to each other. A flat file is where all of that information isn’t in separate tables linked together by keys, but it’s in one giant table.

The benefit of a flat-file is, it’s straightforward. It’s a square. It’s a detailed table, and you don’t have to link to different things. The benefit of relational databases from a computational standpoint and a hierarchical standpoint is, it is in many ways simpler. The big thing is, there’s no duplication of data. If you have a flat file, you might have a lot of repetition and not realize it. Relational databases are easy to navigate, and they’re also easy to identify problems in QA or quality control.

Patient visits

For example, if I want to see that we correctly show all of the patient visits, it’s effortless to see that in a relational database. Whereas if you’re going to see, for example, “Are we accurately capturing all of the denials for a particular patient encounter?” For instance, that’s hard to do on a flat-file because you’d have to do a whole bunch of multiple complex sorting to get them to line up near each other to see that. Diagnosing problems is very hard in that kind of environment. It’s hard to see what’s going on and if something’s working.

We’re continually going from a relational database to a flat file. If you want to generate a report like you want to see all the visits for a particular patient, it outputs as a flat file, but it draws on the relational database to do that. So you get a report out of the flat files. From our standpoint, Apache Health, we’re constantly going from a relational database to a flat-file back to a relational database, but that’s a whole different kind of issue.

Metadata (we should do another day what it is) matters in this kind of thing because most people in medical billing only ever see a flat-file. That’s all we ever see. In addition, we see the output. Also, we see a report. We don’t see the relational database behind it. The problem is (this is why I think the reporting is so wrong in so many systems). Further, we don’t understand how it’s being generated, how it’s drawing from that relational database, what it’s missing, what it’s squishing together, or what it’s overriding and not showing us. What screws us up from a billing standpoint is that the information is lost when you frequently put it into a flat file.

CPT codes

For example, we’ve got multiple denials for a charge, and there are two different CPT codes for that patient visit. This particular report is set up (and I’ve seen this in a lot of systems) because it shows both of the CPT codes on one line. So it’ll show patient visit 5678, and it’ll show CPTs 99214 and G0, one for injection or something like that. One of those got denied. So we’ll see a denial, but we really can’t see from that report which of the CPT codes relate to denials. Or, if there are multiple denials and multiple CPT codes, do all of the denials relate to one of the CPT codes. On the other hand, do each of the denials relate to a separate CPT code? Because it all smushed together in one record. Thus, it makes it very confusing and very hard to disentangle and figure out what’s going on.

As billers, we usually get around this because we see, “Ah, denials for that individual one.” We dive into that patient record and solve the problem for that individual patient encounter. That’s fine from a billing standpoint, but it doesn’t help us take a step back and look at the bigger picture and say, “Hey, where are the patterns? Where are we finding problems?” Because the data in that flat file doesn’t allow you to do that type of analysis.

Why does this all matter?

For example, you got two different denials on two other dates. We submitted the claim. Also, we got a denial. Further, we fixed that problem. I know you’ve seen this problem a ton of times. You hurdle off that problem, and then they’re like, “Oh!” and they give you a different denial. If you have a flat-file output coming back, it may overwrite the old denial. You’ll only see the most recent denial. So once the claim is done and paid or resolved or written off, we only see the last denial. If we want to do some analysis afterward. We don’t see the three, four, five, six, seven, or something like that that. Were done in a row one after the other, after the other where we kept slogging away at that claim. We can’t see that pattern because that flat-file causes us to lose that information.

Why does all this matter? It means that we have to think about what it is that we’re trying to see. The limitations of file structure are like flat files, especially how the report designer created it. If we’re using something that a billing system, a practice management system, or a revenue cycle management system is generating, and if we don’t know how they’ve structured it or what they’ve done behind the scenes, it can be very confusing, very misleading. A lot of information can be lost.

In conclusion

I don’t want to say, caveat emptor. It’s just that thinking about this and realizing that we as a billing industry are predominantly using flat files, which causes a considerable number of problems. We may also believe that we need to start thinking about structure and data instead of slogging away to enrich claims. Also, we will have opened up a tremendous amount of opportunity to do these analyses that then say, “Hey, here’s a pattern that I’m seeing. Now, let’s go solve that problem, so we don’t have these problems again in the future.”

I don’t want to say caveat emptor. It’s just that thinking about this and realizing that we as a billing industry are predominantly using flat files. Hence, this causes a huge amount of problems. Also, we need to start thinking about structure and data and not just slogging away to enrich claims. For example, we will have opened up a huge amount of opportunity to do these analyses. We say, “Hey, here’s a pattern that I’m seeing. Now, let’s go solve that problem, so we don’t have these problems again in the future.”