Figuring out the flow... (1 Viewer)

[zozew]

Hi,

I've built a db for a jail institution and im a little stuck in one area. Ill just give you a short intro first to the structure...

db consists of inmate records, name, prision number, security class and squad name (plus a lot more not relevant for this flow)

Example:
inmateName: John Smith
prisonNo: 2015-999
securityClass: MAX (can be - RDC, MIN, MED, MAX)
squad: A under RDC (total 80 squads under the different security Classes)

Now when a new inmate is booked he is put in the RDC class for a period of time around 60 days. RDC is also a physical place (Reception and Diagnostic Center) then they are re-classified to either MIN, MED or MAX class and assigned a new Squad when they arrive to their new compound MINIMUM, MEDIUM or MAXIMUM

Each Compound RDC, MIN, MED, MAX has a station (Computer running the DB front end and sharing the backend in a shared folder)

So what im looking for is a user friendly way of re-classifying the inmate when its his time to move on

Basic flow:
RDC station flags a set of inmates for transfer to different Compounds, then inmates are physically transferred to their new compounds and then the Compound stations checks the flagged inmates and accepts/finalizes the transfer...

How would this be done best ? a form with some listboxes and check boxes, or just a datasheet form with checkboxes...

any idea is welcome

thx

*attached is one screen grab of the UI for the DB and a popup for the population overview

 

[gemma-the-husky]

do you just have a single record to indicate the placement status? I would have a series of records to build up a history

so a table consisting of:

inmate no.
placement (as a lookup code, so RDC is, say, code 1)
dateofplacement
any other info

so when an inmate is initial placed you get

1234, 1, Dec 14th 2014, etc

when he is reallocated you add a new record

1234, 3, March 17th 2015, etc
(where 3 is the new allocation)

you can easily get a query to establish all inmates currently in RDC to determine whether they are due for progressing, and you have a full record of inmates moving through the system.

I would also add a record type for "left system/transferred/completed sentence etc" to complete the process.

does that seem feasible?

 

[zozew]

Hmm... Not sure I really understand you, all placement fields are lookups, class, squad, status and so on. What I Don't understand is you say add a new record when inmates is reclassified and moves? You mean add a copy of the old record with just a new class? I think I must be misunderstanding you...

My problem is not to actually do the change in the db system it's as easy as clicking the drop down and choosing the new class, what I would like to create is a better way to track the movment by adding the process of an inmate being released from RDC and then the accepting compound acknowledging they have received him by checking it in the inmates record as accepted/received or something like that.

You see I have an audit form that tracks all changes made to the inmates records regarding movements class, status, squad, reformation, discipline etc... So everyday the in charge can check what has been happening in the different compounds... That's the reason I would like a record of each compound actively acknowledging the transfers from RDC compound...

Hope that makes sense...

Check the screen grab I attached... I have tons of info in the records... And dates when they are moved from class to class is a sure thing...

 

[jdraw]

How about a picture of your tables and relationships?
What exactly does this mean

all placement fields are lookups, class, squad, status and so on.

I agree with Dave, although may be saying it differently, that each movement ( or whatever your term is) from one status to another is a transaction that gets recorded and serves as a log of the movements an inmate had and when the movement occurred. You might even want From and To attributes if it's relevant.

 

[zozew]

ok I understand the making a new record for each new movement might be beneficial for a "History", but in my case or as i understand it the way i set it up it works quite well for keeping an eye on amount of inmates per Status, Class, Squad etc. And the way its set up now i would have to make huge changes..uhmm i think..well check the Relations screen grab

The jail is not really interested in keeping track of how many squads the inmates has been in rather the day to day changes and totals. Also the change from RDC to other compound happens once...other changes dont happen to often..

...so maybe version of what you are suggesting like a movements history table for each inmate as a one to many relation where i only track fields pertaining to movement changes...well...thats kinda what i already have in my audit table only that i also track some other info changes as well...

I was thinking of making a form that filters the Audit table so one can see all inmates movements day by day as thats done manually now... (added the audit form screen grab)

The comment i made about lookups is that Status, Class, Squad in tblInmatesProfile all look up the values in the respective tables.

i know you will probably comment on my one to one relations tables but i just thought it would be neat to separate certain data in to separate tables even if they all could live in the same table...maybe im totally wrong...?

 

[gemma-the-husky]

it's not so much the movement history - it's more the concept of designing the data structure to match the real world system you are trying to model. The importance of getting the correct normalised data structure just cannot be overstated.

Simplistically, so you have tables for say

TABLE inmates: (the persons name, address and so on)
-you have some nuances here. If a person reappears as a repeat offender is he the same person, or do you give him a new inmate reference. If you want to count repeat offenders, you may not want to give him a new identity. This needs careful consideration. You may find you need a person table as well as an inmate table, so that the inmate table points to a person, and you can therefore identify that a given person has been an inmate on 3 occasions.

TABLE facilities: (the location and other details of the physical facility)

now you need to consider the relation between inmates and facilities. If an inmate can only be in a single facility, then you could store that facility ID in the inmate record.

However, if an inmate can be in multiple facilities, or more likely if the facility changes from time to time, then it may be better to consider a third "junction" table between an inmate and a facility. "FacilityMembers". This table can include an "inmate reference", a "facility reference", and probably a "transfer date", as well as probably a load of other stuff - such as details of who managed the checks in and out, and so on.

TABLE FacilityMembers (inmateID, FacilityID, datetransferred etc etc)


So all the actual procedures you have to check people in and out, and administer the processes and so forth, are just operating on this data store. So the data store has to be right.

It's an art really. Test the data model against your actual data. Refine and improve the data store. As you start develop you will probably find things that indicate a little more refinement is necessary, so you have to redesign. Getting the data store right makes development much more harmonious

Hope this helps

The jail is not really interested in keeping track of how many squads the inmates has been in rather the day to day changes and totals. Also the change from RDC to other compound happens once...other changes dont happen to often..

as an aside, it's not so much that the jail may not be interested - it's more that the designer may need to build in this way to get the database to work. The jail may not need this information, but surely the local govt administrators would like to know how many inmates are in the system, how long they spend at given facilities, whether some facilities are more effective than others. etc etc

 

[zozew]

Hahaha well there goes my whole db out the window, I have no problem admitting when someone shows me something that looks so much better. My whole approach is based on an existing excel file they called master file with all the inmates just thrown in there. From that I started to try and makes things easier for me hahaha obviously I didn't....

So... You have given me a little taste of how it should really look but I have to admit my limitations I won't be able to do this without further guidance... My dB structure knowledge is as you probably have noticed very linear and simple.

If you could/can would you help me structure it up... And tell me what questions to pose so I can figure out the right solutions.. Im a quick learner as long as I get a push hehe.

This is a probono job where I will actually donate the computer stations so they can start using this...

Thanks for the input I will sleep well tonight and start to think more about this tomorrow... Im on Hongkong time... Cheers!

 

[jdraw]

zozew,

Work through this tutorial.. You will learn how to design a database to support your "business". You have to work through the tutorial to learn the steps involved. There are a number of tutorials on the page - try others as well to gain experience.

More info on Normalization

 

[zozew]

zozew,

Work through this tutorial.. You will learn how to design a database to support your "business". You have to work through the tutorial to learn the steps involved. There are a number of tutorials on the page - try others as well to gain experience.

More info on Normalization

Thanks im reading through the document now...very intresting. Ill have to take out all my fields and determine the type...entity or attribute hehe that will be interesting ill get back and post an update when (if) I get through it hahah :banghead:

 

[zozew]

ok...I've exported all fields and tried to organize/de-compile them according to the normalization rules i read...

I'm having a bit of trouble with an inmates "temporary" status designations. you see they can become an ICA (Inmate Custodial Aide) or a P1 (similar to ICA) or a Jailward he can also loose that status and he can be admitted to the Hospital or the Quarantine Ward...so not sure how to de-compile those.

I've attached an Excel document with my efforts hehe...if anyone can have a quick look and see if im totally off beat??

Now i have changed a few field names and moved things around but mostly to try and make sense of it all...but i think all is quite self explanatory ..

thx

 

[zozew]

It's an art really. Test the data model against your actual data. Refine and improve the data store. As you start develop you will probably find things that indicate a little more refinement is necessary, so you have to redesign. Getting the data store right makes development much more harmonious

ok...ive tried to move around decompile and reconstruct all Entities and attribute. Please have a look at the attached screengrab of my relations and let me know if you see any direct No-No's ive done

Thanks for pushing me towards making a more flexible and better DB structure