Question to our Web Site :-

Subject: Judicial Case Management Data Model
Question: Background Info: I am in the process of designing a database for a legal case management system.

One of the main tables of the system will be the CASE table.
As one would assume, there are various types of actors involved along with their various roles.
For example, I have a CASE_ROLE table tied between the CASE and CASE_ROLE_TYPE tables.

Tied to the CASE_ROLE table is an ACTOR table.

There are many different types of ACTORs in each CASE.

Right now I have sub type tables, PERSON and ORGANIZATION tied to the ACTOR table.

0Under the PERSON table there can be different types of PERSON which would also store their respective information.

For example, under the PERSON table there can be ATTORNEY, JUDGE, OTHER, etc...

I am trying to figure out the best database design to accommodate this ACTOR piece.

Do you have a model that close resembles what I am looking to accomplish?

Right now I have the ACTOR table and tied to it are PERSON and ORGANIZATION.

Under Person I have tied to it ATTORNEY, JUDGE and OTHER.

The primary key in ACTOR is actor_pk.

In the person table I have a compound key of actor_fk and person_pk.

In the ATTORNEY table I also have a compound key of actor_fk, person_fk and attorney_pk.

Is there a better way this should be designed?

Regards

Mike