Looking for a new perspective!

[SimonD]

Is this what you mean by reversing the arrow?

 

[SimonD]

This is my latest: (with database normalization enforced)

I still haven't touched the "arrows" to my many-to-many [AccusedT].

 

[SimonD]

Reversing the arrows on the Junction table will give you a better result. Perhaps I can explain it from a mechanical viewpoint.

You are trying to implement what is called a "many to many" relationships between people and cases. Access doesn't DIRECTLY support that - but it DOES support the Junction table concept. So if you want to use the junction table to enumerate the relationships between two tables with a "many to many" relationship, you want the "many" sides to be on those two tables. The "one" side in the Junction table is because that table enumerates each individual link between a person and a case. Since a person could in theory be charged many times and since a case can involve many accused, the Junction table is merely a list of links.

See example in previous post, would that match your description of a many-to-many relationship?

However ... there is more to consider. You (correctly) said that you have to move the Finding elsewhere because you can actually have multiple disparate findings for a single case with multiple persons. I'll make a suggestion.

Since a person can face more than a single charge and a multi-person case will possibly offer multiple findings, you need to put the finding in a place that is unique to a given combination of person and case. As it so happens, the junction table is a unique place where that combination is already expressed.

I thought of it, that’s why it is there in my new relationships diagram! It made sense!

In your earlier post, 2nd option, you had "Member" linked to "Charge" via the junction table. But you had other tables for which similar concepts might appear. For instance, you had hearings with a simple link between "Case" and "Hearing." But I'll bet that there is such a thing as a continuance or deferral. Does that count as a separate hearing? Or a separate session?

You would have lost your bet on this one, in the military, no such thing. Hearings are just a “session” where the Court is convened, if more than 5 days between adjournments or days with court closed, then it is simply another hearing under the same docket #. If the convening order change, it is simply another case (another docket number). So the relationship will always be 1 case to many hearings (or to 1 hearing, it enough to complete the process).

I'm betting dollars to donuts that some of the people you would list in the Case table are the same cast of characters for a lot of cases? If so, a junction table between Case and "Cast of Characters" might help you take care of some useful queries like, how many cases involve Panel Member John Q Public?

If a member is charged again, I want my database to have a new entry for that member. Since the information associated with that member, such as rank, unit, etc, might change over time, and I want the database to reflect the accurate information on the member for that particular case. (I don’t want to update the information on the member for the latest case, since if I look at that member for the old case, I want to be able to see what unit he/she was back then).

Pardon me if I step into teacher mode. Your central junction table is what we sometimes call the Intersection of its component tables - in this case, Members and Cases. This is where those two sets overlap. There are thus three major places in your data - things that are unique to a Member; things that are unique to a Case; and things that are unique to the combination of a Member and a Case. So when you try to decide where stuff goes, think along the lines of "Does this depend ONLY on the Member?" "Does this depend ONLY on the Case?" "Does this depend on the combination?" That is how you decide where to put things.
Now expand that way of thinking to all of your tables. When you read up on Database Normalization, you will see the "dependency" question in at least some of the references. And that isn't an accident or a happy coincidence.

Past a certain point, some of this discussion will have to fall to you since you are the one who is immersed in the environment where this occurs. We are on the outside looking in, even if doing so with educated eyes. But if we give you good advice, you will quickly learn by doing.

Good luck.

My issue is the interaction between three main tables; I’ll go read up on “dependency”
I have things that are unique to a member, things that are unique to a charge and things that are unique to a case.
Thank you for the pointers! Much appreciated


This diagram is with the arrows reverse, as you suggested.

 

[The_Doc_Man]

Me:

I'm betting dollars to donuts that some of the people you would list in the Case table are the same cast of characters for a lot of cases?

SimonD:

If a member is charged again, I want my database to have a new entry for that member.

Specifically was not considering the accused member, but rather the persons who act as panel members. Legal staff members? Since this is military, members of the JAG group or whatever is your equivalent? You've got a separate table for the accused persons; I was thinking of a "Staff" table for the OTHER entries such as were in the CaseT table of your first post - mostly panel members and alternate panel members. But if that is a jury picked by open lot from a broad population, it is different from a hearing panel where you have multiple court officers. Since I don't know what those positions actually represent, I'll just say that it bears consideration.

Re your post 21: Yes - since you are simulating a many-to-many relationship between your MemberT and ChargeT, the "many" side of the relationships through the Junction table should still be on MemberT and ChargeT, which means the "one" side HAS to be on the Junction table - AccusedT.

Hearings are just a “session” where the Court is convened, if more than 5 days between adjournments or days with court closed, then it is simply another hearing under the same docket #.

OK, but this STILL engenders a question. Would you need to track that session in some way, perhaps if it involved a split session or continuance? That is the question YOU have to answer for yourself, of course - not for me.

I'm sure that in the legal arena, you are aware of the concept of "Discovery" when counselors attempt to gather the facts of the case. Well, what YOU are doing is "discovering" the facts of your problem. And it can be a painstaking process. Part of the process is that you have to elevate the status of each step of your process from "everybody knows how to do this" to "this is the detailed description of the step that everybody knows." Because, of course, Access isn't "everybody" so it knows nothing until you spell it out. But that question about how you would handle sessions is an example of "process discovery" that all of us must experience for our own projects.

Post #23 - look specifically at the link from ConclustionT through CaseT. First, are those actual fields listed or the statuses? (AllChargesWithdrawn seems incongruous with regard to the rest of the content.) The position of the arrow also is confusing since "Sentence" surely is an individual result but "Decision Published" is a case result. Ditto for "Appeal". Also, you show one/one relationship between case and conclusion. If you have multiple Accused for a case, wouldn't a split conclusion be possible?

Also, this is more just a question than anything else... vis-a-vis the comments about hearings, the PK marker for HearingT needs to be consistent with whatever you decide is correct about hearings.

You have said you will read up on dependency. When you do you will determine the difference between an ATTRIBUTE (which becomes a field in some table) and a DEPENDENCY (which might lead to a table to track different entities). I suspect from what you posted that your ImportantDateT, ConclusionT, and HearingT tables would benefit from scrutiny after you've done your reading.

 

[SimonD]

Specifically was not considering the accused member, but rather the persons who act as panel members. Legal staff members? Since this is military, members of the JAG group or whatever is your equivalent? You've got a separate table for the accused persons; I was thinking of a "Staff" table for the OTHER entries such as were in the CaseT table of your first post - mostly panel members and alternate panel members. But if that is a jury picked by open lot from a broad population, it is different from a hearing panel where you have multiple court officers. Since I don't know what those positions actually represent, I'll just say that it bears consideration.

I have created [PersonnelT] that encompasses all staff, from jury (called panel members in the military), to the lawyers, court reporter and finally the Judge. J

OK, but this STILL engenders a question. Would you need to track that session in some way, perhaps if it involved a split session or continuance? That is the question YOU have to answer for yourself, of course - not for me.

It is my understanding that it will track the sessions. By creating a 1-to-many relationship between the [CaseT] and the [HearingT], I will have from 1 to many hearing(s) linked to that unique Case, and each hearing entry will keep track of dates, location, type of hearing, etc. There are no such things as split session or continuance in the military, easier to keep track in this database, I guess!

Post #23 - look specifically at the link from ConclustionT through CaseT. First, are those actual fields listed or the statuses? (AllChargesWithdrawn seems incongruous with regard to the rest of the content.) The position of the arrow also is confusing since "Sentence" surely is an individual result but "Decision Published" is a case result. Ditto for "Appeal". Also, you show one/one relationship between case and conclusion. If you have multiple Accused for a case, wouldn't a split conclusion be possible?

I will remove all the content of the [ConclusionT] and shove it back into the [CaseT]. And regarding the arrow towards “Sentence”, I agree with you, even though here in my office, the admin clerk, who fills the current excel databases, is under the opinion that regardless the amount of offenders (offender: an accused found guilty) there is only one sentence where the Judge enumerates them. For the purpose of keeping track, I will link them to each member under a case.

Once again, thank you for taking the time to give me some pointers!

 

[The_Doc_Man]

Not a problem. I'm sure all of our members are here to "pay it forward" for all the help we have gotten over the years.

I have created [PersonnelT] that encompasses all staff, from jury (called panel members in the military), to the lawyers, court reporter and finally the Judge.

Probably a good idea. You might not have thought about it initially, but if your project works decently for record keeping, SOMEONE is one day going to ask a question about deriving a report on Personnel case load. It ALWAYS happens that when someone sees something all neat and pretty that THEY suddenly think they want something neat and pretty for their own use. And a good design will give you the flexibility to respond to those questions.