relationships for gang database

[DMJ]

This is my first time creating a database from scratch and I seem to running into issues each step. Perhaps my relationships need to be tweeked. Can someone please review them.



Also I need to track inmates who contact inmates as well as associates. How do I do this? CS# field (CS is the inmate #) in the Associates Table and visa versa? Or create table with two CS# fields somehow and an associate field? Suggestions???

[June7]

Not sure you need independent Inmates and Associates tables. Certainly an inmate could be an associate but don't want this person in both tables. Have a People table. Then child tables for incarceration periods and incidents. Surely inmate could have more than one incident? Remove the IncidentID field from Inmates. You have the Inmate CS# in Incidents to establish relationship. Actually, you need a junction table for incidents because can be more than one participant and people can be in more than one incident. Do not put people ID's in Incidents.
PeopleIncidents
PeopleID
IncidentID

Documenting associations gets really confusing.
A associates with B, D, F, M
F associates with A, M, O, S
Therefore we know M associates with A, F and that A and F associate with each other.
Would that be 14 records?
Principal...Assoc
A............B
A............D
A............F
A............M
F............A
F............M
F............O
F............S
M............A
M............F
B............A
D............A
O............F
S............F

[DMJ]

A People table still will not solve the issue of tracking affiliations between gang members.

[June7]

Why not? Just more people for the People table. Source data for affiliations is the People table. The only fields in Inmates I see that might not apply to Associates are ReleaseDate and InmateCS# and that should be in Incarcerations table (repeat offenders?).

BTW, should avoid spaces, special characters, punctuation (underscore is exception) in names. For instance, a recent poster had issue with / in a field name, had to change name to eliminate. Better would be LevelOrTitle. Also, avoid reserved words as names (you have done this so far).

You probably want to get rid of or rename CustomerID field.

[DMJ]

How would I track affiliations when 2 fields would be the same in a table?
I have reviewed the idea of the People table but as this database will be used at a correctional centre the warden does prefer separately the two groups.

[June7]

Maybe the warden doesn't understand relational database concepts. Users should not see the tables. Users should interact with data through forms and reports. Queries would filter the records and display on forms/reports. This gives the appearance of separate tables because users see restricted selection of the records. Consider this issue with separate tables. What about when an Associate comes in to be an Inmate or Inmate leaves and later appears as an Associate to someone - put these individuals in both tables? This violates a major principle of relational database - do not duplicate data. You would have to maintain two records for same person (address, phone number, etc). If the goal is a working application, the warden (user) should leave the HOW to the developer (you).

One method of tracking the affiliations is as I showed. This arrangement would require a double join to the People table, i.e. joining People to each of the ID fields of the junction table so as to retrieve the people details for each field. Then you can query the Principal field for a peopleID and return all Associates and view details for each individual.

[DMJ]

Thank you for your helpful feedback. I have had other assignments take over my work days lately; sorry I haven't responded.
Met with team here at work and we are still set on keeping the two tables Inmates and Associates. I created a junction table from Inmates to Incidents and one for Associates to Incidents. Do Junction tables only join 2 tables or can 3 be incorrporated? I have deleted relationships as you suggested. Attached is new relationship report. What now?

[June7]

Would help if that image weren't sideways. Doubt if I will have any additional comments and what I have offered already still holds.

[orange]

I recommend that you write a 2 or 3 line description of each of the Entities(Tables) indicating what each is and its intended purpose. This will be helpful in understanding exactly what data you intend to keep/use and how the data relates. It can also be useful for documentation as your system evolves.
Make sure the whole team agrees with the definitions -- experience says this will prompt discussion, but will result in a better understanding of the "things" involved.

There are tutorials related to database design at
http://www.databaseanswers.org/tutorials.htm

Have you identified a list of business facts that your database must/should support?
Have you created some test data (both valid and invalid cases) to test your data model?

Create a data model; test your model with your test cases; reconcile any problems/issues that evolve; adjust (the model and/or the test data) as necessary.

Good luck with your project.