Normalization or Add new fields to the existing table?

I am having a hard time trying to figure out the correct/best way to make
this table(s) to track the progress of a juvenile once released from our
facility, ie: whether the juvenile has re-offended as a minor.

Necessary Items: (The Easy stuff)
[JuvenileID] - provided from the Juvenile Table
[ExitType] - Successful or Unsuccessful

(The Hard Stuff)
If [ExitType] = "Successful",
then [Subsequent Adjudication(Reoffended)] = Yes or No.
If [SubsequentAdjudication] = Yes,
then [WhatTypeofAdjudication] = arry(referred to StateLevel,
NoChange, Other)
elseif [ExitType] = "Unsuccessful",
then [UnsuccessfulType] = array(Mental Health, Medical,Program
Non-Compliance, Other)
end if

Do I have 5 fields or 4? And how to make it, pardon the phrase,
"Idiot-proof"?

On Tue, 6 Jan 2009 10:46:00 -0800, Joseph
wrote:

I am having a hard time trying to figure out the correct/best way to make
this table(s) to track the progress of a juvenile once released from our
facility, ie: whether the juvenile has re-offended as a minor.

Necessary Items: (The Easy stuff)
[JuvenileID] - provided from the Juvenile Table
[ExitType] - Successful or Unsuccessful

(The Hard Stuff)
If [ExitType] = "Successful",
then [Subsequent Adjudication(Reoffended)] = Yes or No.
If [SubsequentAdjudication] = Yes,
then [WhatTypeofAdjudication] = arry(referred to StateLevel,
NoChange, Other)
elseif [ExitType] = "Unsuccessful",
then [UnsuccessfulType] = array(Mental Health, Medical,Program
Non-Compliance, Other)
end if

Do I have 5 fields or 4? And how to make it, pardon the phrase,
"Idiot-proof"?


Well... the critical question should actually not be "do I have n fields in my
table" but "do I need another table"?

Tables don't use branching or if-then logic or arrays. These are appropriate
for procedural language - but relational databases are emphatically NOT
procedural!

I would suggest that you need a one to many relationship from the Juveniles
table to an Outcomes table. This might have fields like

Outcomes
OutcomeID Autonumber Primary Key
JuvenileID link to the main table
Outcometype "Reoffended", "Mental Health", "Medical", ...
OutcomeDate
Comments

One offender might have multiple outcomes (i.e. they could reoffend three
times and also have medical issues).

You would probably want a Form with a Subform, and probably some other small
auxiliary tables such as a table of OutcomeTypes.
--

John W. Vinson [MVP]

"John W. Vinson" wrote:

Well... the critical question should actually not be "do I have n fields in my
table" but "do I need another table"?
Correct, I should have phrased it like this.


Tables don't use branching or if-then logic or arrays. These are appropriate
for procedural language - but relational databases are emphatically NOT
procedural!
I understand that, it was only for an explanation of the needed data.


I would suggest that you need a one to many relationship from the Juveniles
table to an Outcomes table.
One to one relationship will do, as it is just to measure whether we
provided enough training, or fortitude to do the right thing, for the
juveniles not to revert back to the reasons that they are in our program in
the first place. And the first time is all it takes. But then there are the
exceptions. We have had juveniles stealing food because the parents were not
there to provide the support the juvenile's needs, and get caught.

This might have fields like

Outcomes
OutcomeID Autonumber Primary Key
JuvenileID link to the main table
Outcometype "Reoffended", "Mental Health", "Medical", ...
OutcomeDate
Comments

One offender might have multiple outcomes (i.e. they could reoffend three
times and also have medical issues).

You would probably want a Form with a Subform, and probably some other small
auxiliary tables such as a table of OutcomeTypes.
--

John W. Vinson [MVP]

I have come up with a good way to normalize it, I think.
ExitStatus
-ExitStatusID autonumber
-JuvenileID - from Juvenile table
-ExitStatusTypeID - from table ExitStatusType (Default Successful from table
SuccessfulType)
-UnsuccessfullTypeID - from table UnsuccessfulType (Default N/A from table
UnsuccessfulType)
-SuccessfulTypeID - from table SuccessfulType (Default No Additional
Adjudications from table SuccessfulType)
-SubAdjudTypeID - from table SubsequentAdjudicationType Default N/A from
table SubsequentAdjudicationType)

And then on the forms, have the Last three in a series of Select or If-Then
to make the Cmbo.Enable = True.