Need help with ER diagram

Question

I am making database on Disaster Management. There are four tables.

1. DisasterEvent (eventid, name, glideID, status, description, date_occurred, date_ended, disaster_type)

2. People(eventid, num_deaths, num_injuries, num_affected)

3. Homes (eventid, num_homes_destroyed, num_homes_damaged, total_insured_loss, total_uninsured_loss)

4. Place (place_id, city, state, country, postal_code)

In my design, I have one to one relationship between People and DisasterEvent , and one to one relationship between Home and DisasterEvent because for a particular disaster there is only one People table that we are concerned and same for Home table. Now, Place has many to many relationship with DisasterEvent because at particular place there can be many disasters over time and same disaster could occur many place at once.

What am I trying to achieve is able to map number of affected people by the particular disaster event at certain place. For this I think there needs to be three way relationship among DisasterEvent, Place and People. How do I achieve this?

Answer 1

Since you have a 1-1 relationship between DisasterEvent and People now, you can not distinguish any relation between People and Place. If several Places are related to a single DisasterEvent, all those places are related to the same instance of People.

If you want to map the People affected by the Event at a Place, you should relate People to Place instead of to DisasterEvent.

As long as an event always occurs at at least one place, you can always relate your People to your DisasterEvent via the Place where it occurred.

On a sidenote, I would use Location instead of Place, and maybe the more precise Victims for People, as well as maybe Damage for Home.