Run into a Jam with my normalization (1 Viewer)

[George10988]

Hello everyone, I am creating a database for a local police department. The database is supposed to contain info related to juvenile's, their offenses, home situations, and cases.

I have a normalized setup which currently includes 3 tables.

One table is the "Home Bio" table which contains things such as the father and mother's name and address/ phone, number of siblings and things like that.

The next table is the Juvenile Bio table which contains things such as the juvenile's name, age, birth date, race, etc.

The last table is the "case" table which contains things like date arrested, date released, and things pertaining to their actual case.

My dillema is that the juvenile officer wants me to create a database with all their possible offense codes. For example Burglary could be offense code 0349 or something like that. She wants to be able to start typing in a number or offense in the data entry form I have setup and have it auto predict what she is typing in as she types it. I also am not sure how to relate this table to my other tables. I tried setting it up to my case table as a one to many relationship because obviously one case of arrest can have many offenses; however, I get an error saying that records in my main table might not exist in my secondary table. I'm not sure how to related that database because it will already have hundreds of pre-issued codes and offenses. One or multiple of these codes must be assigned to each juvenile on a case by case basis.

 

[boblarson]

You would have the fourth table

tlkpOffenses

OffenseID - Autonumber (PK)
OffenseCode - Long Integer (but if the code can have letters then make it text)


And then you would have a FIFTH table (a junction table because the juvenile could have more than one offense):

tblJuvenilesOffenses
JuvenileOffenseID - Autonumber (PK)
JuvenileID - Long Integer (ID from the Juvenile table)
OffenseID - Long Integer (ID from the Offenses table)
CaseID - Long Integer (ID from the Case table)
DateOfOffense - Date/Time

and anything else that pertains to that single offense for that single person.

I noticed you didn't mention that your three tables had a Primary Key field. Make sure you do and it is best if it isn't text but is a numeric key instead.

 

[boblarson]

Oh and I forgot to say - on your form you will be using subforms for not only the Offenses but that the Juvenile table seems to be what would end up as the record source for the main form and then the Home Bio would be used as a subform on it as well as the Offense table would be used as a subform on the Juvenile form. The case form would be separate completely.

 

[George10988]

Boblarson: I will try your approach.

As for the form, there is a main form she wants me to base off a form they currently use. Its basically one big form with all the data from every table. The way I have been doing it is I made a query "qryDatabaseEntry" to pull in all the data that pertains to that form then I of course attatched each respective input text box to its respective field. Is this the incorrect procedure for this? She wants to be able to print this form because the form itself requires a signature from the juvenile's parents

P.S. thanks for the quick response, I have also setup primary keys for the previous tables. All of them are autonumbers except for my case table which is the last in the chain. The reason for that is because their case numbers look something like this 0-122345

 

[boblarson]

You cannot use one big form/query to do this. You will need to use subforms but you can make those subforms look like they are integrated into the rest of the form.

Also, I would use an autonumber for the CaseID. They can still have the case number (which would be TEXT because of the dash). They don't need to see behind the scenes and then if they someday change the way the case numbers look, or work, etc. your program will need no modifications whatsoever.

 

[George10988]

Thanks alot Bob!

 

[George10988]

Boblarson:

I still cant seem to wrap my head around this. Is there anyway you could take a look at the attatched SS of my relationships view and give me some advice?

Thanks in advanced.

 

[boblarson]

Well, first off your Cases table either needs to be linked to the tblJuvenileOffenses table. You would want to unlink the cases to the Juvenile table and change the link to tblJuvenileOffenses table and then link OffenseID from the tlkpOffenses to OffenseID in the tblJuvenileOffenses table.

 

[George10988]

So would the below SS be correct?

 

[boblarson]

Well, not quite. Here's my take on what I think it should be like. I don't have all of the fields you might have in some of the tables so I couldn't include them but hopefully this will help.

 

[George10988]

Oh boy! thats much more complex than I anticipated! I did not realize that I could have normalized even further in that manner. I will have to rework everything I've got right now. One more question. I understand some of the tables you have there are many to many relationships. I dont quite understand. What is the purpose of that type of join?

Also if I am to have a father and a mother as a parent, would it be best for each to have their own table or is joining them into one table fine?

 

[boblarson]

Oh boy! thats much more complex than I anticipated! I did not realize that I could have normalized even further in that manner. I will have to rework everything I've got right now. One more question. I understand some of the tables you have there are many to many relationships. I dont quite understand. What is the purpose of that type of join?

Also if I am to have a father and a mother as a parent, would it be best for each to have their own table or is joining them into one table fine?

They are all one-to-many basically if you look again. But there are places where you will need to have flexibility. Each juvenile could have more than two parents. Each parent could be parent to more than one juvenile and not necessarily the same for each parent of one of the juveniles. That is why I split some of it out to different tables. Also, a parent for one could be there uncle, aunt, grandmother, grandfather, etc. so the relationships are able to be defined there as well instead of just father or mother. The tlkpParentRelationships is just that

01 Father
02 Mother
03 Sibling
04 Uncle
05 Aunt
06 Grandmother
07 Grandfather

etc.

and then you store the ID in the JuvenileParents table.

Same with Cases. A case could have more than one juvenile associated with it, could it not? And each case could have more than one offense associated to it, could it not?

 

[George10988]

Here is my updated relationships. I'm hoping you could maybe give me some more advice. I just want to make sure it is all good so that I don't have to come back and do this. This is my first database ever :\

Heres my logic to it.

Each Juvenile can have only one guardian

Each Juvenile can have multiple homes ( Im guessing if they move or something)

Each juvenile can have multiple parents and their respective relationships

Each juvenile can have more than one gang ( if they change I guess)
- I realize that also each gang can have more than one juvenile so I should
create a junction table for the two.

Each Juvenile can have more than one case and also each case can have
more than one juvenile

Each Case can have more than one offense and also each offense can have
more than one case

Each Case can have more than one officer and also each officer can have more than one case.

Finally each juvenile can have more than one companion. I realize that the
companion might be in the database themselves too, I could normalize this more; however I am not sure how to due to the fact that they would need their own separate record as well. I talked to one of the juvenile officers and he told me that if they bring in two kids together sometimes only one is charged and the other not put into the system. If they both are charged, on the full report for kid #1 will be Kid#2's name as a companion and vice versa.


Also one more question. Why was it that I can not have one giant form for data entry? What will be the problem if I do that in regards to using subforms