Database Normalization Opinions (1 Viewer)

[magster06]

Hello All,

I have a database that has a few tables and I was wondering if anyone would be able to tell me if I am close to having a Normalized Database.

I have attached a pic of my table relationships.

I think I need to have a FK for CFD Witnesses, Focus, Complainant, and Civilian Witnesses (but I am not exactly sure of this).

Also, would I need to create a seperate table for the complaints? I also thought of a creating a seperate table for the time line, but not sure how to give this a FK to the main table.

 

[jzwp22]

You have 4 tables (tblCivilianWitnesses, tblFocus, tblComplaintInfo, and tblCFDWitness) that all have similar name fields (first name, last name etc.). Why not have 1 table for information pertaining to people. From the looks of it, you have many people associated with a case (one-to-many relationship).

Can a person be associated with many cases?

Your PSU table needs quite a bit of work. You have a bunch of fields related to dates of events that occur related to a case. That says that a case can have many related events (one-to-many relationship), so it would be best to have a table that holds those events. Also, if the events of one case play out the same for other cases, that would suggest that you have another one-to-many relationship. When you have 2 one-to-many relationships between the same two entities (cases and events), then you have a many-to-many relationship and therefore need a junction table.

First a table to hold the generic events:

tblEvents
-pkEventID primary key, autonumber
-txtEventName

This table would hold the events such a sent to fire chief, received from fire chief, filed, case received from PSU, case received from Admin etc. You will have to provide more detail on the other fields in the PSU table, but I suspect that there are several other fields that are just tracking events related to the case.

Now, the junction table (relate the specific events to each case):

tblPSUEvents
-pkPSUEventID primary key, autonumber
-fkPSUID foreign key to tblPSU
-dtePSUEvent (date field)
-fkEventID foreign key to tblEvents

 

[magster06]

Your PSU table needs quite a bit of work.

I knew it would. I am very new at this. I appreciate you taking the time to give me some pointers.

Also, if the events of one case play out the same for other cases, that would suggest that you have another one-to-many relationship. When you have 2 one-to-many relationships between the same two entities (cases and events), then you have a many-to-many relationship and therefore need a junction table.

Each case is unique and does not have any bearing on other cases.

First a table to hold the generic events:

tblEvents
-pkEventID primary key, autonumber
-txtEventName

Can the pkEventsID be the Case Number? or does it have to be another unique identifier?


The following are used to help track the case and they are all dates:
Case Received
Assigned to RO
Assigned to Investigator
Sent to PSU
PI13 Sent
PI13 Served
Date to Fire Chief
Date From Fire Chief
Date to Director
Date From Director
File Date

The following is information collected when a caller calls in to file a complaint:

Call Taker
Call in Date
Call in Time
Incident Date
Incident Time
Caller Involved
Caller the Patient
Complaint Description
Any Witnesses
Have Witness Info
Complaint Address
Complaint City
Complaint State
Complaint Zip
Statement Concluded
Will Sign Statement
Call End Time

 

[jzwp22]

Each case is unique and does not have any bearing on other cases.

But the field names in your PSU table indicate that certain basic events are the same for all cases. Perhaps it would be better for you to explain the purpose of each field in your PSU table?

Can the pkEventsID be the Case Number? or does it have to be another unique identifier?

The event table is just a table to hold generic events that have nothing to do with any particular case. When you relate an event to a case, you would do that in the junction table tblPSUEvents.

The case number field you have in the PSU table is fine where it is. The general recommendation is that the primary key of a table should have no significance to the user. So in your case, the case number does have significance to you, so it would be better to have a separate field for the primary key (I use the autonumber field for that) and use that to make joins to any related tables. You can still create an index with your case number and have it unique but I would not use it to make joins to related tables.

 

[magster06]

But the field names in your PSU table indicate that certain basic events are the same for all cases

Yes, you are correct. The date tracking is the same for all cases. I will try to implement what you have suggested.

lol, I thought I had almost completed my project :banghead:

 

[jzwp22]

Please post back with any questions.

 

[magster06]

jzwp22,

After looking at my tables, it still looks like I just need another table for my "time line" and can do without the junction table.

I probably did not explain things as well as I could have.

Each case will have its own set of dates. No dates will cross over to other cases.

I am new at this, so maybe I am not seeing what you are.

Here is an example of how the dates are used:

Case 2013-001: Case Assigned: 01/01/2013 ; Case sent to PSU: 01/02/2013; etc...

Case 2013-002: Case Assigned: 01/04/2013; Case sent to PSU: 01/05/2013; etc...

Each set of dates applies to only one Case Number.

 

[jdraw]

spellThese seem to be a sort of status that each case goes through. I could see these values being in an Event or Status table and used for every Case.

The following are used to help track the case and they are all dates:
Case Received
Assigned to RO
Assigned to Investigator
Sent to PSU
PI13 Sent
PI13 Served
Date to Fire Chief
Date From Fire Chief
Date to Director
Date From Director
File Date

So part of the database could use such data for trend analysis; efficiency review; current status of each and every case....

Do cases always get to a Case Number; do you ever have a Withdrawn or Rejected event/status?

You may find a data model or a few that would help at
http://www.databaseanswers.org/data_models/index.htm

 

[jzwp22]

magster06,

What you described should be handled with the structure I presented in my earlier post and which jdraw confirmed in his post. You should have a table of events (or statuses) and relate the particular events to the case via the junction table.

To illustrate, I have attached a sample database. Please take a look at the form frmPSU and try it out. You will also need to take a look at the relationship diagram to see how I have related the tables.

Call Taker
Call in Date
Call in Time
Incident Date
Incident Time
Caller Involved
Caller the Patient
Complaint Description
Any Witnesses
Have Witness Info
Complaint Address
Complaint City
Complaint State
Complaint Zip
Statement Concluded
Will Sign Statement
Call End Time

With respect to the above list dealing with the complaint. How do you define a case? a complaint? And BTW what is a "PSU"?

 

[jdraw]

jzwp22,

Yes that's the concept. Good example.

We had a thing years ago where companies could apply for Grants/Loans/Assistance under various Govt Programs. There were a\ll sorts of rules depending on the location,Program,total Govt monies currently "loaned" to the company, and a theme of the proposed work against 7-8 general lines.

You had things like
PreApplicationConsultation
Application
ApplicationFeeRecived...
ApplicationReviewAndAssesment
ApplicationRejected
ApplicationWithdrawal
ApplicationAwaitingClientInformation
ApplicationAwaitingFunding
ApplicationFundingApproved
.....

seems much the same "type" of thing.

In our case you there was a "flowchart type logic involved", that identified what status the project was in and what the next possible statuses were.
If the Application was withdrawn or rejected, there wasn't much you could do-- perhaps Apply again. As with most govt things, there was an accumulator invoked when status changed (the ball is in your court and you have taken xx days.. )--everyone was concerned with govt efficiencies/program efficiencies, so turn around was important.

I see this similar to poster's situation.

 

[magster06]

Hello guys,

Again, I am very new at this and I still do not have a grasp on Access yet.

Do cases always get to a Case Number; do you ever have a Withdrawn or Rejected event/status?

Yes, every case generates a Case Number and once a number is generated, then it will be in the system forever.

Please take a look at the form frmPSU and try it out.

I will look at this when I get back into the office on Monday.

How do you define a case? a complaint? And BTW what is a "PSU"?

Whenever a complaint is received and has some basis for an investigation, then a case is formed. PSU just stands for our Professional Standards Unit team, that investigates the case.

The cases travel through many hands and we want a way to keep track of where the case is at all times.

**Again, please know that I am very grateful for you guys taking the time to help!

 

[jzwp22]

Whenever a complaint is received and has some basis for an investigation, then a case is formed. PSU just stands for our Professional Standards Unit team, that investigates the case.

What happens to complaints that do not have some basis for investigation & thus do not become cases? Do you still record the complaint and if so where (what table)?

Can more than one case result from 1 complaint?

 

[magster06]

What happens to complaints that do not have some basis for investigation & thus do not become cases? Do you still record the complaint and if so where (what table)?

At this time, PSU does not keep a record of complaints that do not have enough information to move forward with a case.

 

[jzwp22]

OK, regarding the following fields, can you explain what the data type is for each field and a brief explanation of what type of data will be entered? I am trying to understand if you have other relationships in your data.

 

[magster06]

jz, I posted a message in your profile

 

[jzwp22]

I prefer to work through the forum rather than e-mail. All I really care to see at this point is some representative/sample data in the tables. To reduce the size of your database, run the compact & repair utility within Access and then zip & post the file. If the file is still too big, create a new blank database and import just the tables & data. You can now delete some of the data and just leave some sample data. Run the compact & repair & then zip.

 

[magster06]

Here is the stripped down version.

I am in the process of creating a table for the time line and one for the complaint details.

 

[jzwp22]

OK, I took a quick look. You have repeating information in several fields of your PSU. To make data entry easier, it would be best to put the possible choices in a table and link that table to your PSU table. For example, you can have a table of complaint origins/originators with 3 records: department, civilian, mayor. You would replace your complaint orig field in the PSU table with a foreign key. This would only hold true if there is only 1 origin/originator per complaint. Is that true? Or can you have many?
Similarly if you have a certain choices for allegations you can do the same (put in a separate table and relate back to table PSU. Again that would be how you set it up if you only have 1 allegation per complaint. I would do the same with the disposition field as well. Put all possible dispositions in a table and relate that to PSU table.

You also have 2 fields related to people: call taker and reviewing officer. Since you have more than one person, you have a one-to-many relationship. I assume that a person can be involved in more than 1 case, so you have another one-to-many relationship. This again describes a many-to-many relationship, so you will need a table to hold all people and a junction table for people related to PSU (tblPSUPeople).

Does a complaint always deal with only 1 address? If yes, then you can leave the address fields where they are. You might consider having a table of cities/states and then linking those back into your PSU table.

BTW, I see you are from Ohio--Columbus area I assume based on your data. I'm in the Dayton area & originally from Cleveland.

 

[magster06]

jz,

I apologize. I left out all of my fill tables for the combo boxes to reduce the size of the db.

I have tables for most of the fields that you have mentioned... Allegation, Action, Complaint Origination, etc...

You also have 2 fields related to people: call taker and reviewing officer. Since you have more than one person, you have a one-to-many relationship. I assume that a person can be involved in more than 1 case, so you have another one-to-many relationship. This again describes a many-to-many relationship, so you will need a table to hold all people and a junction table for people related to PSU (tblPSUPeople).

The junction thing still confuses me, but I will give it a try.

Do you feel that I should also create separate tables for the dates (I call them time line) and complaint information?

I wish there was some way to get my whole db on here so you can see how it comes together and if it actually does come together, lol.

BTW, I see you are from Ohio--Columbus area I assume based on your data. I'm in the Dayton area & originally from Cleveland.

Good to hear from a fellow Ohioan.

 

[jzwp22]

The junction thing still confuses me, but I will give it a try.

Give it a shot & then post what you have & we'll take a look at it. The structure will be similar to the tblPSUEvents we discussed earlier

Do you feel that I should also create separate tables for the dates (I call them time line) and complaint information?

No, separate tables for similar information generally goes against normalization rules. Could you consider the time line as just a series of events (and thus put them in tblPSUEvents?