Modeling a complex data relationship

Is it possible, using the Dictionary to efficiently map a "multi-level" data relationship?

Here's what I mean:

The proposed data entity is a "claim". This entity has numerous properties, including patient information and provider information. One of the properties of the claim is a service line. A claim can have any number of service lines, and each service line has its own properties (includiing one or more service codes or diagnosis codes).

It seems I have a one to many to many to many (too many's) relationship.

Would I have to create the "claim" as one dictionary item with a field for "service line" (which can be multi-valued) and a separate item for a "service line" which would have a multi-valued field for "service code" or "diagnosis code"? if I do that, how would I be able to create the dictionary report which wiould effectively display these relationships? It would be relatively easy, if one dictionary item could reference another dictionary item, but that does not appear to be an option.

Can you propose a solution?

Is this something which would represent a good feature request?

Thanks!

Tom

Tom Tomasovic May 06, 2011 20:27 Answered

Please sign in to leave a comment.

2 comments

Tom, this is a great question! There's lots of ways to eat an elephant but here's what I'd do:

I'd create a dictionary element for Claim, ServiceLine and Diagnosis:

For the Claim, add your fields and include a member field "ServiceLineCollection" and make the type "ServiceLine"

for the definition of ServiceLine, add your fields and include one called DiagnosisCollection. Make its type "Diagnosis"

Finally, define the fields for Diagnosis. I've taken a whack here:

For ServiceLineCollection in the Claim and the DiagnosisCollection in ServiceLine, I've used the constraints window to indicate that there needs to be at least one, but potentially many, of these values (doubleclick the field name to open the constraints window):

One final note: you've probably noticed that if you add a field and set its type to another dictionary element name, CaseComplete recognizes that and shows it in a distinguishing color. If you hover your mouse over it, it'll show you the meaning defined for that element:

When you run your Dictionary report, the relationships and arity (number of items permitted in a value) are nicely documented for your DBA:

This, of course, is a logical data model that your DBA (or you, if you're the lucky guy) get to translate into a physical model. If this is going into a relational database, you'll need to add intersection tables to hammer it into normal form.

Hope this helps, Tom!

Permanently deleted user May 06, 2011 21:14 0 votes

Ron,

Makes sense to me. I was kind of hoping to be able to do a parent/child type display in my requirements document, but this will probably work (for the time being). Perhaps, this can be paired with a more visual entity relationship diagram.

I'm just thinking that an application with lots of complex relationships (or just with lots of fields for any given entity) could get a bit messy to handle, since you really have to create two entries for every intersection. It would be much easier if there were a way to relate collection fields to entities.

Thanks for the help!

Tom

Tom Tomasovic May 09, 2011 13:47 0 votes