Database tables relationship

[josephbupe]

Hi,

I am re-visiting my database table relationship for criminal incidents.

Six tables are involved plus a junction table to make it seven tables in total.
I need someone to review this relationship and advise whether or not it is correctly set up.

The relationships are as follows:

1. A person can belong to more than one incident:

t_persons (one-to-many) t)_incidents_persons


2. A person can only have one nationality by birth

t_persons (one-to-one) t_countries

3. An agency can have more than one incident

t_agencies (one-to-many) t_incidents


4. A status (e.g. closed incident) can belong to more than one incident

t_status (one-to-many_ t_incidents

5. A keyword (i.e. offence type eg theft)

t_offencekeywords (one-to-many) t_incidents

[josephbupe]

Sorry here is the screenshort of the EER diagram.

Regards.

 

Joseph

[Barand]

First, a comment or three on your current model.

1. Person table contains the country_id twice.
2. Person table should not contain the country currency and currency code fields. They should only be in the country table.
3. Country-person is a one-to-many relationship, not on-to-one (one country has many persons)

Notes on my model (I only included the key fields for brevity):

 

An incident will have several statuses over its lifetime. I included from and to dates so that historical data is available (how many open cases were there each month, for instance) The "to_date" field in the current status record would contain 9999-12-31. This enables you find the current status as the one where the current date is between the two dates in the record.

 

Is it possible that one agency is in charge of an incident but later an other agency takes control? If so you may need to take that same approach with the agency-incident relationship.

 

Incidents may require several keywords. For example, take the "Jack the Ripper" murders in 19th century London. Several incidents all with keywords "Murder, mutilation, prostitute"

 

[josephbupe]

That's what I needed.