Two one to many's on one table

[RipperT]

This is not a typical business application so I don't know how easy it will
be to help. I'll do my best to explain it. If further info is needed, let me
know. And thanks!

tblInmates
PK InmateID

tblHearings
PK HearingID
FK InmateID

tblCharges
PK ChargeID
FK HearingID

tblSanctions
PK SanctionID
FK ChargeID

Each inmate can have mutiple hearings; each hearing can have multiple
charges; each charge can have multiple sanctions.

Now, once an inmate accumulates too many charges in a short period of time,
he gets classified to segregation. He can later be released from
segregation and later reclassified again. I need a table to track
these seg classifications, but I am stuck on how to relate them to the
charges table. Ideally, a single seg classification (one) would have
multiple charges (many), but the charges already exist as children of
tblHearings and could not be added as children of the parent
tblSegregation. How can I relate tblCharges and tblSegregation so I can
extract an inmates seg classifications and the charges that go with them?
Many thanks,
Ripper

 

[Tom van Stiphout]

On Wed, 3 Dec 2008 18:35:43 -0500, "RipperT" <<RiPpErT>@nOsPaM.nEt>
wrote:

Your last sentence (no pun intended) made the most sense: what is it
that you are trying to accomplish with this app. In most larger
databases there are relations that COULD be created, but they don't
further the objectives of this application, so the smart programmer
does not create them.

If you want to track which inmate has segregations, and which charges
led to those segregations, the database design should include:
tblSegregations
PK SegID
FK InmateID
SegDate
NumberOfDays

tblChargesThatLedToSegregations
PK SegID
PK ChargeID

So with that last table you have a classic Many-to-Many relation
between Charges and Segregations.

-Tom.
Microsoft Access MVP