A newbie is asking for relationship help...

[trake]

Hi. I'm brand new to this sport. I've just been given the task to help create a database and wondered if anyone would have the time to help set up an initial relationship? This deals with handling workman's comp cases. I'm using FM Pro 10.

1 ---There's people - name (fname and lname), address, phone etc, and notes (maybe a pic).

2 ---There's insurance companies (some people *may* have more than one insurance company. Each person will have a case id number. And it would be great to look up insurance companies and view the different people.

3 ---There's attorney's. With company name, contact name, address, phone, notes, etc... Each person will have another case id number.

For the people, there will be about 6 options for the type of case (I guess radio buttons?). Let's call it case1, case2, etc...

I also need an area (I think like a line item) where notes can be kept in a running fashion for each person/case... Date xx/xx/xxxx Notes: xxxxxxxxxxx, etc.

I'll need to be able to look at a table and view which insurance company was contacted last and what the payment, if any, was. Payments will always be from insurance companies.

I need area to eventually put very basic billing info, but I thought if anyone has something like this or is willing to share/help I could get a jump start on this. If anyone can point me to a resource that would be great as well.

Thank you!

[ESpringer]

Hi beeswax!

I'm not at all an expert on this sort of database structure, but I can make one "common sense" remark about the structure as you describe it. That is, if you think about which *tables* you'll need, I think what you've written collapses two tables that should be distinct: "people" and cases. Perhaps it seems obvious that people will only have one case in the system at the same time, but you want a database structure that is robust enough to handle it if suddenly it turns out that one person has multiple claims in process. (And you might call that table "claimants" rather than "people" -- unless you want to make an insiders' joke about the humanity of the attorneys in your other table. :

As you describe the task so far, it sounds to me as though *cases* will be the primary basis for your most crucial layouts, with related fields and portals to other tables.

As for how to track communications and payments, I'd encourage you to go with related tables here, too. Although you may enter information into those tables only *from* a Cases layout, and a person using the database shouldn't need to *think* about the fact that the data is stored in a related table, there are various advantages to doing it this way (as with line items on an invoice). In particular, it will enable you to slice the data in different ways, such as being able to bring up an insurance-company-based layout on which you see (portal-based) info about all recent communications and payments with which they've been involved...

I'm replying only because you haven't yet got another response. I have no doubt that someone will have something closer to an expert's advice...

[Vaughan]

Answer the following questions:

Can a Case can have more than one person associated with it, and can a person be involved with more than one case?

Can a case have more than one insurer, and can an insurer be involved with more than case?

Can a case have more than one attorney, and can an attorney be involved with more than case?

(You may see a pattern emerging here.)

This question determines whether you have one-to-many or many-to-many relationships between the entities. A many-to-many relationship requires a "join" file to resolve.

Looking at it again, the Payments thing needs some thought. The payments may be associated with insurance companies, or a separate entity itself.

[trake]

Thank you so much for the quick reply!!!

Claimant sounds much better than people. I never even thought of having a cases type of layout. I do believe that would simplify things in the long run while allowing expansion.

My stumbling block is whether to actually make these portal based and/or not. It sounds now as if it would be the way to go.

I'm digesting a FM book, and trying to figure this stuff out (it's been years since I've done any full relational database). I need to get out the flat file thinking.

So if I have cases as a main layout of such that could then springboard to claimants, attorney's, insurance company, and then finally some sort of billing. As well as have options for the 6 (I believe) types of cases.

Now how to exactly relate these things is a bit difficult for me. The join layout concept has me stumped. For some reason I can understand a join layout to products and line items, but when it comes to this thing I go blank.

Then comes the problem of how to keep track of basic billing. But now that cases is in the picture, I think I can bill based upon the cases themselves even though any collections would be coming out of an insurance company. Hmmm.

How to exactly relate cases-claimants-attorneys-insurance companies is quite the mystery to me now and I believe a join table of sorts will be required.

I'm off to the drawing board now.

[trake]

You guys are great. Thank you again for your time.

A case will probably never have more than one person (or claimant) with it.

A case could have more than one insurer (although it's not very common).

An insurer can and probably will be involved in more than one case.

A case will have only one attorney (unless that attorney changes hands to another - which can happen ever so often).

An attorney can have more than one case.

I guess I'm now answering my own questions, although I still haven't quite got the concept of linking these things and envisioning how they'd look on a screen for someone.

Whether to portal, look-up table, or other stumps me for some reason.

And yes, payments I need to think out. Payments will always be by insurance companies and tracked by various phone calls and/or letters via a possible line item "notes" section.