Picking the best data structure - use a joining table?

Need help Database
1

I’m trying to decide on the best way to structure my database, in terms of connecting different types together. I know there are a few different ways to do it, and I read the data structure page in the Bubble manual so I’m familiar with the 3 options under the “Example scenarios” section. But still not quite sure what to pick for my use case.

The project I’m working on connects drugs, studies and claims all together.

Here are the basic use cases:

  • A user can pick a drug and see all the studies and claims for that drug.

  • A user can pick a claim, and see all the drugs with that claim, and also the studies for that drug supporting the claim.

  • A user can pick a study, and see what drug was in it, along with a list of claims for that drug (specific to that study)

Right now there’s a lot of redundancy - I have them all connected like so:

  • Drugs connect to a list of claims and a list of studies
  • Claims connect to a list of drugs and a list of studies
  • Studies connect to a list of drugs ( I haven’t connected them to claims, not sure if I need to, since I can lookup the claims of the connected drugs)

I also went ahead and created what the manual describes as a “joining table”.

It’s a data type called “DrugClaimStudyDose” with the field types of Drug, Claim, and Study, and the text field of “Dose”.

“Dose” comes from the study table, and is needed because some Drugs have different claims at different dosages.

So in this “DrugClaimStudyDose” data type, I create a new thing for each drug + claim + study + dose.

Now…the manual says this “joining table” above is more scalable. I haven’t quite wrapped my head around how I’d use it, I guess I would just make EVERY page that data type, instead of a “Drugs” page and a “Claims” page, etc?

Any suggestions on the best way to approach this? I’m not sure all the redundancy is needed, and like the manual warns, there’s a chance of data consistency problems since it’s easy to forget to assign all the types together, every time.

Thanks in advance.

Nate

2

Yeah if you want to be able to do easy and efficient Search operations across all these types in Bubble, this is the way.

3

Thanks, so is the “joining table” still needed or useful for anything?

4

I find that suggestion rather strange, TBH.

5

I think it’s due to the size limitations of lists. If you’re going to have thousands of entries in a list, it’s better performance-wise to have the joining table

6

The docs are wrong. Lists hold 100K items. Test it for yourself.