Issues with complex database structure

rafael.cunha · 2021-01-08T21:40:04.118Z

Hi there!
I am creating an web app for a while now and we have launched a few months ago for one organization.
Since the launch we have gotten requests from other organizations around Sweden so I am currently updating the application in other to be scalable.

Today I started working on a new database structure (since the old one was not scalable) and I came across a bunch of problems.

Noisify Database test - Page 21980×1180 51.1 KB

This is my thought how it should work… the database is much more complex than that but I tried to simplify it in order to get my questions answered. All the issues are related to dynamic content and many-to-many relationships…

Lets say we get a new User that creates an Activity. By creating an activity it should be linked to the Organization by doing a “workflow”.

If later on the website I want to show all activities in a city… do I need to add it as well to the city so the city database look like:

City

City name
List_districts
Country
List_organisations
List_Activities
List_Users

Is it enough to have it the way it is? Or do I need to create all these fields?

I am used to work in many-to-many databases.
Do I need to create a workflow to update every single field when a user create an activity?
Workflow 1 - Create activity in “Activity_db”
Workflow 2 - Add activity to “Organisation_db”
Workflow 3 - Add activity to “District_db”
Workflow 4 - Add activity to “City”

At the moment I am doing like that, but I must be doing something wrong… It should be automatically as soon I create and activity and link it to an organization simply since the organization is in that city?! Or… do I have to do it like that? Is that even scalable?

Thank you for the help!

rafael.cunha · 2021-01-08T22:30:19.901Z

Update

I have read the “Issues with complex database structure” which still doesn’t give me an answer on what I shall do.

nfisher · 2021-01-08T23:57:46.855Z

The most efficient database does not hold duplicate data, although there are some circumstances where it may be beneficial in a many to many relationship to store data on both tables if it is very one sided, or to avoid a complex search. This is all part of streamlining your application.

In you instance, your Activity is a One to many relationship so I would say you should not store the activities on the city when you can just search for the activities filtered by city.