Ideas for structuring DB

[rbpd5015]

How would you guys go around setting this database up? Any input would be great.


I will be creating a database for police cases.

A Case number is issued for every arrest I make. This case number is assigned to the event not the arrestee he is assigned to the event.

So I figure I have the following tables.

1) Event Table (Time, Date, Loc of Incident etc)
2) Person Table (Going to attempt to use a table I have from another db called suspectdata. Although all these people are suspects they could be victims also. I am not sure if I need to create two separate databases for suspects and victims).
3) Officer Table (This is all the officer in my department so I can easily call them to them as arresting officers, witness etc)
4) Charge Table (A table listing all the state charge numbers, disc etc.
5) Vehicle Table (A table with fields for vehicles)
6) Property Table (A table with fields for property)
7) Narrative Table (A table where I store the narrative for each case)


No the issue is this I need to create a set using this and this is where I get a bit confused.

Each CASE Number issued by the Police Department can only have one event.
Each CASE Number can have unlimited persons listed as victims and suspects
Each CASE Number can have unlimited Charges
Each CASE Number can have unlimited Officers involved, but only one arresting officer
Each CASE Number can have unlimited Vehicles involved
Each CASE Number can have unlimited Property involved
Each CASE Number can have unlimited narrative (If there is a co defendant (same case num, might be a different narrative)

I know I need to create and believe I need to make event the Main Table then link each other table in a 1-many aspect to the main table.

So tell me if I got this right create a field in the Event Table called SuspectId and then link SuspectId in Event to SuspectId in suspectdata inside of a set in a 1-many option?

My question is if I end up have four people listed as for a certain case since I only have them grouped as suspects, how will I know which one is the victim which one is the suspect?

If I sound lost I am sorry.


Any help would be greatly appreciated.

Matt

[Cheryl Lemire]

Re: Ideas for structuring DB

Hi Matt,

So tell me if I got this right create a field in the Event Table called SuspectId and then link SuspectId in Event to SuspectId in suspectdata inside of a set in a 1-many option?

You sound like you are on the right track here.

My question is if I end up have four people listed as for a certain case since I only have them grouped as suspects, how will I know which one is the victim which one is the suspect?

You could add a field to that table and select suspect/victim.

Hope that helps.
Good luck

[DaveM]

Re: Ideas for structuring DB

5) Vehicle Table (A table with fields for vehicles)

police vehicles??


I also agree with cheryl about the field to mark victim\perpetrator. That way you eliminate one whole table. It may evn be possible to add the offices to this same table and mark the arresting officer with a field.

Each CASE Number can have unlimited narrative (If there is a co defendant (same case num, might be a different narrative)

could you put the co defendants in the same as the suspect table? would mean duplicating the fields for all the data like: name, address and name2 and address2 or can there be just too many?

I suppose you are goint to link by case number???

Dave

[Cheryl Lemire]

Re: Ideas for structuring DB

Nice catch on the linking field Dave. I misread

So tell me if I got this right create a field in the Event Table called SuspectId and then link SuspectId in Event to SuspectId in suspectdata inside of a set in a 1-many option?

Matt, in each of your sub-tables, you would have the case number field and that would be the linking field to your parent table in all cases.

[Tim Kiebert]

Re: Ideas for structuring DB

So tell me if I got this right create a field in the Event Table called SuspectId and then link SuspectId in Event to SuspectId in suspectdata inside of a set in a 1-many option?

Many of the relationships you describe look like many to many. So using the Person id in the event table as the link would limit you to only one person even if you use a one to many (1-n) . You could put in multiple fields,like suspect1, suspect2, victim1, victim2 etc. But then you you have to guess on the high side how many of each type of person would be related to a case to allow for enough people. Then most of the time many of those fields will be empty(not that that is a problem these days with disk space but say in a browse you would need to diplay them all in case they contained data.) Also the link you would use is 1-1 as there should only be one person per id.

Using the Case id in the person table would limit the Person to only belonging to one event. In the case of a repeat offender you would either need another case id field in the person table or add the suspect a second time to the person table. But then you would have duplicate records. (not good)

I would use a third table that links events to persons. Call it say, Pers_Involved. At the very least it would have two fields, case_id and Person_id. When looking at a case you would have a set that has Event as parent linked 1-n to Pers_Involved linked 1-1 to Persons.

Although this is resolving a many to many relationship in the real world, as far as the data base is concerned, you in affect have one 1-n relationship between Event and Pers_Involved (Like Invoice_Header and Line_Items in Alphasports) and a look up 1-1 between Pers_Involved and Persons(like Line_Items And Products in Alphasports)

This way the person table can be used solely for holding actual personal data , whether that is suspect, victim, or officer. You could keep the officer data in the same table but you could very well have reasons to keep officers separate. My intuition would say keep it separate. cause they are on the opposite side of the operational fence. If you did keep them seperate you would have a table called Off_Involved to link them to the case. To record the arresting officer you could either store that separately in the event table or have a logical field in the Off_Involved table that marks that officer. If the latter you would need to add field validation in the Off_Involved table to limit one true value per event_id

Pers_Involved becomes the table where you add a person to the case and describe the relationship between the person and the case such as, is this person a suspect or a victim, maybe a rating on the involvement. The same person could be a suspect in one case but a victim in an other.


I would say you would do the same with the Charge table where the Charge table becomes basically a pick list of what to add to the event/case. using a table called say, Evnt_Chrgs.

Well, I have run out of time. Hope this helps.

PS As an aside be careful using the word 'case' within your aplication as it is a reserved word within Alpha

[dmartens]

Re: Ideas for structuring DB

Matt,

To extend what Tim said below, a good design practice would be to allow any number of related elements in your tables where real life might allow any number to be present.

For example, each event could relate to many people (e.g., Joe, Jim and 23 other people witnessed a crime). Additionally, each person could be related to many events (Joe witnessed a crime today and also witnessed one last week). On this basis, for your table, events relate to people in a many-to-many form.

If you choose not to allow an unlimited number of related elements, you will either 1) artificially limit yourself (e.g., building in an ability to handle only, say, 3 relationships, but a case comes up that has 20 relationships between elements); or 2) need to make the built-in number so high that it handles all cases (e.g., 200?). The latter choice could be very wasteful in typical cases (lots of blank fields that are maintained and read from the database).

Many-to-many relationships require more up-front work, but they are far more (infinitely) flexible so many people seem them as being worth the effort.

Many-to-many relationships are often implemented through intersection tables. For the event/people relationship, you could have:

an event table that has:
* an event_ID field (unique and auto-increment)
* a description field

a people table that has:
* an person_ID field (unique and auto-increment)
* a name (and other interesting info)

an intersection table that has:
* an event_ID
* a person_ID

Using this system, the event_ID field in the event table is linked to the event_ID field in the intersection table. Similarly, the person_ID field in the people table is linked to the person_ID field in the intersection table.

In this scheme, you would have one entry in the intersection table for each combination of event and person IDs that is appropriate. Thus, it takes three tables to store and freely relate information that could be stored in 2 tables if no relationships were present.

When you want to show the details of an event, you need only get all entries in the intersection table that have an event_ID that corresponds to the event you want. From each entry, you can get the person_ID. From that person_ID, you can get more info relating to that person from the people table.

Judging from the helpful spirit other respondents have shown, I'm sure any number of people will be happy to help you further if what any of us has written is not clear.

Good luck,
Dave

[Tim Kiebert]

Re: Ideas for structuring DB

Matt, just to link my post and Dave's the table I called Pers_Involved plays the role of Dave's intersection table.