Best practice for creating database for my use case

[veeslop]

Hey ! First, please Excuse my english …

Been a few days i’ve been wondering about the best way to organize my database for a new project. Been trying & been browsing through the forum posts, but can’t seem to find out the best solution, so figured i’d ask you

Let’s say i have 5 data types (Accounts, Employees, Lines, Phones, Queues)

An account can have multiple employees / employees can have multiple lines& multiple accounts & multiple phones / a line is linked to one account, one phone, but can contain multiple queues … Everything is kinda linked together… So my question would be :

How would you approach this ? Should Accounts have a list of lines ? Or should each Lines have a relationship to Accounts - should phones have a list of multiple lines, should everything have LIST of everything ?

What would be the best practice for this to be able to scale ?

Thanks a lot

[sat_miha]

It depends on the size of the lists within each type and the importance of the search functionality for the ‘list holder’ type.
Bubble’s documentation suggests not storing lists that exceed 100 items. Working with nested lists is usually easier than working with separate types.

[veeslop]

That’s one of the choices I have, … would you make a list for every type in each type ? Both ways ? And wouldn’t that slow down the app because a RG of Accounts would load every item in every list that account could have (lines, employees, phones …) ?

Thanks

[boston85719]

@veeslop

And wouldn’t that slow down the app because a RG of Accounts would load every item in every list that account could have (lines, employees, phones …) ?

No that is not how related data fields work in relation to performance and loading of data within an RG. It has been falsely presented over the years on the forum with few being able to correct the false assertions.

When using an RG to load all Accounts, any related fields will be treated only as text and load only the unique IDs of those related things, so the list of lines, employees, and phones will just show as unique IDs for each item within those lists, not the entire set of data fields for each type, so if for example on the employee data type you have a dozen fields such as name_first, name_last etc. those fields are not part of the data load. Anybody who states otherwise is speaking from an uninformed perspective. I’ve posted on this in the past and quoted Bubble support to give the answer ‘from the horses mouth’.

However, if any element within the repeating group, such as a text element, uses an expression to reference any field from a related field (ie: current cell’s Accounts employees first item name_first), then All data fields of the related type will be part of the data load…so, that brings up a best practice to follow when wishing to show related items like that, especially when it may just be one or two fields from the related type, is to on the type (in this example Accounts) have a field for that related field (so have a text field that would be employees name_first on the Account data type) so that you can show that value in the RG without incurring the slowdown incurred from having to load All the fields of the related type.

@veeslop

An account can have multiple employees

@veeslop

employees can have multiple lines& multiple accounts

Depends on you in this situation, since both directions of relatedness for the employee and account is a list (ie: each employee has multiple accounts and each account has multiple employees)…just think about the way in which this data will be used by the app users to make the decision. Whichever list is expected to be smaller (will an employee have more accounts or will an account have more employees), put the related field as a list.

@veeslop

a line is linked to one account, one phone

On the line data type put a related field to Account and field for phone

@veeslop

a line is linked to one account, one phone, but can contain multiple queues

You don’t mention if anything will have multiple queues, so I assume there is not…so, on the queue data type put a field of Account, since one queue will have only one account presumably.

@veeslop

Should Accounts have a list of lines ?

There would be no reason for this based on the description you gave as it doesn’t look like accounts have lines, but instead accounts have employees and employees have multiple lines, so there is not direct relationship between an account and a line, so you put onto the employee data type the line as a list field

@veeslop

Or should each Lines have a relationship to Accounts

Is there actually a relationship between accounts and lines?

[veeslop]

Thanks a lot for taking the time …

@boston85719

No that is not how related data fields work in relation to performance and loading of data within an RG. It has been falsely presented over the years on the forum with few being able to correct the false assertions.

That’s some crucial information ! Thanks!

@boston85719

Is there actually a relationship between accounts and lines?

Lines, employees, phones all belong to an account / the employee has a phone, to which lines are associated to (which are all owned by the account) … queues belong to lines

If i get it right: if i make a relationship between lines & employees and that this employee is linked to an account, i dont need to make a relationship between accounts and lines ? I could get the results via a Search For Lines where Employee’s Account = This Account ? Would that be optimized ? Or should i do Lists everywhere to link everything ?

Having a hard time explaining - hope that will make sense

[veeslop]

@boston85719

@veeslop

a line is linked to one account, one phone

On the line data type put a related field to Account and field for phone

Trying to figure this out completely : Why shouldn’t i put a Lines field as a list in phone data type ?

[boston85719]

@veeslop

Why shouldn’t i put a Lines field as a list in phone data type ?

I didn’t completely understand your data until the reply

@veeslop

Lines, employees, phones all belong to an account

On a Line, Employee and Phone have a single field that is related to Account

@veeslop

queues belong to lines

How many queues will belong to a line? If it is more than 50 or so, you’d rather make it so it is a 1:1 relationship by having every Queue have a related field to the Line…if it is less than 50 you could put onto the Line data type a field that is a list field related to Queues

@veeslop

the employee has a phone

On Employee have a single field to the related Phone…On Phone have a single field that is related to Employee (this way it doesn’t matter which is the more accessible data type, both will allow you access to both types of data)

@veeslop

a phone, to which lines are associated to

If you expect a Phone to have less than 50 related Lines you can put onto the Phone data type a related field that is a list of Lines

@veeslop

if i make a relationship between lines & employees and that this employee is linked to an account, i dont need to make a relationship between accounts and lines ?

True as you can go from Lines’ employee’s account…but of course we can find ourselves based on a feature or the way searches are performed in our apps use case making relationships to aide in ease of development and perhaps search filtering.

@veeslop

Or should i do Lists everywhere to link everything ?

it is not about lists everywhere, but as much as possible you want to make the relationships to avoid searches, but it is up to the way the data interacts with each other as well as the way you use the data in the app that can help determine which is better, a relationship using lists or 1:1 relationships

[veeslop]

Great ! Thank you again for the advice, will do that way !