Please tell me if my database design is good to go or if I am running into a problem of loops or performance issues when the application will scale and data is growing inside the database

Need help Database
1

Hello guys,

I am building an application where my users are employees of marketing agencies that are creating SEO reports for their customers.

I am having slight problems with deeply understanding the best practices of bubble database design. I have read and many times consulted Petter Amlies Book about performance but still this is not an easy task and I would like to be sure and not realize 6 months in that I made a huge and dumb mistake.

Please tell me what you think about the following database structure:

Database
Database2891Ă—1383 305 KB

My questions are:

  • Is the reference from Agency DT: listOfReports to Report DT and the reference back from Report DT: belongsToAgency to Agency DT a problem?
    → (the reason I am asking is because if a page is of type Report DT then this page has potentially a loop: Report DT → belongsToAgency → Agency DT → listOfReports → has all Report DTs inside which again each of them have a belongsToAgency → Agency DT → listOfReports and so on … Or is this not true?
  • Should I think about joinTables such as: AgencyReportJoinTable DT which has two fields: Agency: Agency DT and Report: Report DT or maybe satellite DTs?
  • If I use satellite DTs would’n that mean that I need way more Do a search for-operations leading to potentially an extremely high and unwanted WU usage?
  • What type should a page have other than Report DT if the page is all about the information gathered in Report DT (which is probably always the case)?

The reason I chose to go the route displayed in the image above is that I get each bit of information very easy from every point of the application. From Report DT I can access every bit of information (like: display all keywords for one report: Current pages Report DTs keywords, or show all agency reports: Current Users Agency DTs ListOfReports.

My instinct says there is something not okay. (I have tried this structure in a project and on one page I have a table element displaying all keywords. Sometimes the page does not load at all or loads very slowly. I have not clue if this is related to the structure or the table element being in beta.)

What else I am thinking about:
Maybe move the Keyword DT to a MongoDB instance. This datatype can easily grow big (each report can have something around 300-1000 keywords), each agency creates many reports a month and we should have many agencies that again have many employees. So my solution should be ready to scale.

I would be really happy to start a discussion and maybe have a sparing partner/feedback on my thoughts.

Thanks!

1 Like
2

Hi,

In a quick view in your chart, I pay attention about the use the fields as a List.
If this field will increase so much, I suggest create a new satellite table to avoid this list

All the best

1 Like
3

Yes. Agencies could have thousands of reports. Lists should only be used for a small number of things (perhaps a list of users in an Agency).

Why? You can always add it later. Unless you have a good reason to, don’t overcomplicate it.

1 Like
4

Thank you for your input. Yes that is also what concerns me. But how for example keep track of all reports created by an agency? There must be some kind of connection. Or do you mean something like:

Create a new field in Agency DT:
Agency DT
ListOfReportIDs: Report ID DT

Create a new datatype:
Report ID DT
Report ID: Report DT
Agency: Agency DT

Or what do you mean exactly?

Thank you for your insights!
Cheers

5

Hi thanks for your input as well.

Yes I agree but how to keep things connected?

To keep data connected. Otherwise I would have Agencies and Reports but without a connection. Or not?

Thank you!

6

Report has an Agency field.

If you know the Agency, you can find the reports with Do a search for Reports where Agency = (the relevant Agency).

If you know the Report, you can find the Agency by referencing Report’s Agency.

You’ll also have privacy rules set up so that reports are only visible when Current User’s Agency is This Report’s Agency.

3 Likes
7

@hi_bubble

Great adives from @georgecollier
In special about privacy rules. But this you can apply in the end steps of the project.

All the best

1 Like