Need help with a complex relationship

[dtown82]

So I am working on a database that is very complex. One issue I am having is with one of the complex relationships. I work in the claims adjusting field so lets say I have a house that had a fire. I would create a record for this particular claim. This house would have several rooms in it. This claim would also have several different parts of the claim that would need to store information pertaining to those same rooms. So the "Claim" record would be the parent to the Rooms/Areas but also to the different parts of the claim such as the Building and the Personal Property. Each Room/Area would have several items that would go under the Personal Property part of the claim, and several items that would go under the Building part of the claim. I would need to call the information from either the Room/Area or the Personal Property or the Building. So, for instance, I would need to go into the layout for the Claim. Inside that, I can go to the Personal Property part of that claim, bring up a Room/Area, and add inventory to that Room/Area. Then I can repeat the process on the Building Portion og the claim. The information won't overlap and I don't want to have to duplicate the Room/Area records for each part of the claim.

 

The parts of the Claim could essentially be thought of as "Sub-Claims", a child to the "Claim."

 

I attached my best attempt to create an ERD of this relationshipProjects-SubProjects-Areas-ScopeItems-ERD.pdf

[bcooney]

Quick google got me here: http://federated-ins-claim-erd.wikispaces.com/Federated+Insurance+Claim+ERD

 

So, yes, this can get complex. Looks like the approach that they took is to have "Activities" as children of a claim, and Activities have types.