A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Two one to many's on one table



 
 
Thread Tools Display Modes
  #1  
Old December 4th, 2008, 12:35 AM posted to microsoft.public.access.tablesdbdesign
RipperT[_2_]
external usenet poster
 
Posts: 30
Default Two one to many's on one table

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




  #2  
Old December 4th, 2008, 05:36 AM posted to microsoft.public.access.tablesdbdesign
Tom van Stiphout[_2_]
external usenet poster
 
Posts: 1,653
Default Two one to many's on one table

On Wed, 3 Dec 2008 18:35:43 -0500, "RipperT"
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


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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:05 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.